Field projections

Do not confuse fields with columns. Columns are only associated with tables, and column projections use conventional dot notation of the form name_1.name2 for a table and column, respectively. A field is a component data type within a ROW type. With ROW types (and the capability to assign a ROW type to a single column), you can project individual fields of a column with single dot notation of the form: name_a.name_b.name_c.name_d. HCL® OneDB® database servers use the following precedence rules to interpret dot notation:

1. table_name_a . column_name_b . field_name_c . field_name_d
2. column_name_a . field_name_b . field_name_c . field_name_d

When the meaning of a particular identifier is ambiguous, the database server uses precedence rules to determine which database object the identifier specifies. Consider the following two statements:

CREATE TABLE b (c ROW(d INTEGER, e CHAR(2)))
CREATE TABLE c (d INTEGER)

In the following SELECT statement, the expression c.d references column d of table c (rather than field d of column c in table b) because a table identifier has a higher precedence than a column identifier:

SELECT * FROM b,c WHERE c.d = 10

To avoid referencing the wrong database object, you can specify the full notation for a field projection. Suppose, for example, you want to reference field d of column c in table b (not column d of table c). The following statement specifies the table, column, and field identifiers of the object you want to reference:

SELECT * FROM b,c WHERE b.c.d = 10