Using Dot Notation

Dot notation (sometimes called the membership operator) allows you to qualify an SQL identifier with another SQL identifier of which it is a component. You separate the identifiers with the period ( . ) symbol. For example, you can qualify a column name with any of the following SQL identifiers:

• Table name: table_name.column_name
• View name: view_name.column_name
• Synonym name: syn_name.column_name

These forms of dot notation are called column projections.

You can also use dot notation to directly access the fields of a named or unnamed ROW column, as in the following example:

row-column name.field name

This use of dot notation is called a field projection. For example, suppose you have a column called rect with the following definition:

CREATE TABLE rectangles
(
   area float,
   rect ROW(x int, y int, length float, width float)
);

The following SELECT statement uses dot notation to access field length of the rect column:

SELECT rect.length FROM rectangles
   WHERE area = 64;

Selecting All Fields of a Column with Asterisk Notation

If you want to select all fields of a column that has a ROW type, you can specify the column name without dot notation. For example, you can select all fields of the rect column as follows:

SELECT rect FROM rectangles
   WHERE area = 64;

You can also use asterisk ( * ) notation to project all the fields of a column that has a ROW data type. For example, if you want to use asterisk notation to select all fields of the rect column, you can enter the following statement:

SELECT rect.* FROM rectangles
   WHERE area = 64;

Asterisk notation is easier than specifying each field of the rect column individually:

SELECT rect.x, rect.y, rect.length, rect.width
   FROM rectangles
      WHERE area = 64;

Asterisk notation is valid only in the projection list of a SELECT statement. It can specify all fields of a ROW-type column or the data that a ROW-column expression returns.

Asterisk notation is not necessary with ROW-type columns because you can specify the column name alone to project all of its fields. Asterisk notation is quite helpful, however, with ROW-type expressions such as subqueries and user-defined functions that return ROW-type values. For more information, see Using Dot Notation with Row-Type Expressions.

You can use asterisk notation with columns and expressions of ROW data types in the projection list of a SELECT statement only. You cannot use asterisk notation with columns and expressions of ROW type in any other clause of a SELECT statement.

Selecting Nested Fields

When the ROW type that defines a column itself contains other ROW types, the column contains nested fields. Use dot notation to access these nested fields within a column.

For example, assume that the address column of the employee table contains the fields: street, city, state, and zip. In addition, the zip field contains the nested fields: z_code and z_suffix. A query on the zip field returns values for the z_code and z_suffix fields. You can specify, however, that a query returns only specific nested fields. The following example shows how to use dot notation to construct a SELECT statement that returns rows for the z_code field of the address column only:

SELECT address.zip.z_code 
   FROM employee; 

Rules of Precedence

The database server uses the following precedence rules to interpret dot notation:

1. schema name_a . table name_b . column name_c . field name_d
2. table name_a . column name_b . field name_c . field name_d
3. column name_a . field name_b . field name_c . field name_d

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

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;

For more information about precedence rules and how to use dot notation with ROW columns, see the IBM Informix Guide to SQL: Tutorial.

Using Dot Notation with Row-Type Expressions

Besides specifying a column of a ROW data type, you can also use dot notation with any expression that evaluates to a ROW type. In an INSERT statement, for example, you can use dot notation in a subquery that returns a single row of values. Assume that you created a ROW type named row_t:

CREATE ROW TYPE row_t (part_id INT, amt INT);

Also assume that you created a typed table named tab1 that is based on the row_t ROW type:

CREATE TABLE tab1 OF TYPE row_t;

Assume also that you inserted the following values into table tab1:

INSERT INTO tab1 VALUES (ROW(1,7));
INSERT INTO tab1 VALUES (ROW(2,10));

Finally, assume that you created another table named tab2:

CREATE TABLE tab2 (colx INT);

Now you can use dot notation to insert the value from only the part_id column of table tab1 into the tab2 table:

INSERT INTO tab2
   VALUES ((SELECT t FROM tab1 t
      WHERE part_id = 1).part_id);

The asterisk form of dot notation is not necessary when you want to select all fields of a ROW-type column because you can specify the column name alone to select all of its fields. The asterisk form of dot notation can be quite helpful, however, when you use a subquery, as in the preceding example, or when you call a user-defined function to return ROW-type values.

Suppose that a user-defined function named new_row returns ROW-type values, and you want to call this function to insert the ROW-type values into a table. Asterisk notation makes it easy to specify that all the ROW-type values that the new_row( ) function returns are to be inserted into the table:

INSERT INTO mytab2 SELECT new_row (mycol).* FROM mytab1;

References to the fields of a ROW-type column or a ROW-type expression are not allowed in fragment expressions. A fragment expression is an expression that defines a table fragment or an index fragment in SQL statements like CREATE TABLE, CREATE INDEX, and ALTER FRAGMENT.