Selecting Columns That Contain Row-Type Data

A row-type column is a column that is defined on a named ROW type or unnamed ROW type. You use the same SQL syntax to query a named ROW type and an unnamed row-type column.

A query on a row-type column returns data from all the fields of the ROW type. A field is a component data type within a ROW type. For example, the address column of the employee table contains the street, city, state, and zip fields. Figure 133 shows how to construct a query that returns all fields of the address column.

SELECT address FROM employee

address     ROW(102 Ruby, Belmont, CA, 49932, 1000) 
address     ROW(133 First, San Jose, CA, 85744, 4900)
address     ROW(152 Topaz, Willits, CA, 69445, 1000))

·
·
·

To access individual fields that a column contains, use single-dot notation to project the individual fields of the column. For example, suppose you want to access specific fields from the address column of the employee table. The following SELECT statement projects the city and state fields from the address column.

SELECT address.city, address.state FROM employee

city             state

Belmont          CA 
San Jose         CA
Willits          CA

·
·
·

You construct a query on an unnamed row-type column in the same way you construct a query on a named row-type column. For example, suppose you want to access data from the s_address column of the student table in Figure 128. You can use dot notation to query the individual fields of a column that are defined on an unnamed row type. Figure 137 shows how to construct a SELECT statement on the student table that returns rows for the city and state fields of the s_address column.

SELECT s_address.city, s_address.state FROM student

city             state

Belmont          CA 
Mount Prospect   IL
Greeley          CO

·
·
·

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.name_2 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. Informix 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

Using Field Projections to Select Nested Fields

Typically the row type is a column, but you can use any row-type expression for field projection. When the row-type expression itself contains other row types, the expression contains nested fields. To access nested fields within an expression or individual fields, use dot notation. To access all the fields of the row type, use an asterisk (*). This section describes both methods of row-type access.

For a discussion of how to use dot notation and asterisk notation with row-type expressions, see the Expression segment in the IBM Informix: Guide to SQL Syntax.

Selecting Individual Fields of a Row Type

Consider the address column of the employee table, which contains the fields street, city, state, and zip. In addition, the zip field contains the nested fields: z_code and z_suffix. (You might want to review the row type and table definitions of Figure 127.) A query on the zip field returns rows for the z_code and z_suffix fields. However, you can specify that a query returns only specific nested fields. Figure 139 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

z_code

39444
6500
76055
19004

·
·
·

Using Asterisk Notation to Access All Fields of a Row Type

Asterisk notation is supported only within the select list of a SELECT statement. When you specify the column name for a row-type column in a projection list, the database server returns values for all fields of the column. You can also use asterisk notation when you want to project all the fields within a ROW type.

Figure 141 uses asterisk notation to return all fields of the address column in the employee table.

SELECT address.* FROM employee

address   ROW(102 Ruby, Belmont, CA, 49932, 1000)
address   ROW(133 First, San Jose, CA, 85744, 4900)
address   ROW(152 Topaz, Willits, CA, 69445, 1000))

·
·
·

The asterisk notation makes it easier to perform some SQL tasks. Suppose you create a function new_row() that returns a row-type value and you want to call this function and insert the row that is returned into a table. The database server provides no easy way to handle such operations. However, Figure 143 shows how to use asterisk notation to return all fields of new_row() and insert the returned fields into the tab_2 table.

INSERT INTO tab_2 SELECT new_row(exp).* FROM tab_1

For information about how to use the INSERT statement, see Modifying Data.