Column Expressions

A column expression specifies a data value in a column in the database, or a substring of the value, or a field within a ROW-type column, or a field in a BSON column.

(1)
Column Expressions

1 
2.1 table.
2.1 view.
2.1 synonym.
2.1 alias.
2.1  column? 1  [  first , last ]
2.1 1 ROWID
2.1  row_column
2.2.1 .*
2.2.1 + 2 . row_field_name
1  row_col_expr
2.1 .*
2.1 + 2 .row_field_name
1  BSON_col_expr
2.1 .*
2.1 + 3 .
2.2.1 BSON_field_name
2.2.1 BSON_array_position
Notes:
  • 1 HCL OneDB™ extension
  • 2 Use path no more than three times
  • 3 Use path no more than 32 times
Element Description Restrictions Syntax
alias Temporary alternative name for a table or view, declared in the FROM clause of a query Must return a string. Restrictions depend on the clause of the SELECT statement in which alias occurs Identifier
BSON_array_position A positive integer that represents the position of a value in an array, starting with 0 for the first value. Must be preceded by all ancestor field names.
BSON_col_exp Expression that returns a BSON column name Must be of type BSON. Expression
BSON_field_name BSON field name Must be a literal BSON field name. Can be a multilevel field identifier, up to 32 levels. All ancestor field names must be included. Using Dot Notation
column Name of a column Restrictions depend on the SQL statement where column occurs Identifier
first , last Integers indicating positions of first and last characters within column The column must be of type CHAR, VARCHAR, NCHAR, NVARCHAR, BYTE, or TEXT, and 0 < first = last Literal Number
row_col_expr Expression that returns ROW-type values Must return a ROW data type Expression
row_column Name of a ROW-type column Must be a named ROW data type or an unnamed ROW data type Identifier
row_field_name Name of a ROW field in the ROW column or ROW-column expression Must be a member of the row that row-column name or row_col_expr orrow_field name (for nested rows) specifies Identifier
synonym , table, view Table, view, or synonym (for the table or view) that contains column Synonym and the table or view to which it points must exist Database Object Name

You qualify a column name with a table name or alias to distinguish between columns that have the same name but are in different tables. You use dot notation to select a field from a ROW column. You use dot notation to select a field from a ROW or a BSON column.

The following examples illustrate some formats of column expressions:

  • Unqualified column name: company
  • Column name that is qualified by table name: items.price
  • Substring of a character-type column: catalog_advert [1,15]
  • Multilevel BSON field identifier: bson_col.person.cars.1

In syntax contexts that support a default value for a column, you can omit the default column expression.

Example: Qualify columns with table names

The following statement, two column expressions qualify the column name, customer_num, with the table names customer and orders: 

SELECT * FROM customer, orders 
   WHERE customer.customer_num = orders.customer_num;

Example: Qualify columns with table aliases

The following statement includes column expressions that qualify column name, customer_num, with the table aliases c and o:

SELECT * FROM customer c, orders o
   WHERE c.customer_num = o.customer_num;

Example: Select a field from a ROW column

The following statements creates a table with a ROW column that is named rect and that has four fields: 
CREATE TABLE rectangles
(
   area float,
   rect ROW(x int, y int, length float, width float)
);
The following statement uses dot notation to access the length field of the rect column: 
SELECT rect.length FROM rectangles
   WHERE area = 64;

Example: Select a multilevel field from a BSON column

The following statements create and populate a BSON column that is named bson_col: 

CREATE DATABASE testdb WITH LOG;
CREATE TABLE IF NOT EXISTS bson_table(bson_col BSON);

INSERT INTO bson_table VALUES(
   '{person:{givenname:"Jim",surname:"Flynn",age:29,cars:["dodge","olds"]}}'
   ::JSON::BSON);

Nested in the person field is an array that is named cars, which has two values. The values in arrays do not have field names. You specify array values with numbers that represent the position in the array, starting with 0 for the first value.

The following statement selects the second value in the cars array that is nested in the person field:

SELECT bson_col.person.cars.1::JSON FROM bson_table;

  (expression)  
  {cars:"olds"}