ROW, Unnamed (IDS)

An unnamed ROW type contains fields but has no user-declared name. An unnamed ROW type is defined by its structure. Two unnamed ROW types are equal if they have the same structure (meaning the ordered list of the data types of the fields). If two unnamed ROW types have the same number of fields, and if the order of the data type of each field in one ROW type matches the order of data types of the corresponding fields in the other ROW data type, then the two unnamed ROW data types are equal.

For example, the following unnamed ROW types are equal:

ROW (lname char(15), initial char(1) fname char(15))
ROW (dept char(15), rating char(1) name char(15))

The following ROW types have the same number of fields and the same data types, but are not equal, because their fields are not in the same order:

ROW (x integer, y varchar(20), z real)
ROW (x integer, z real, y varchar(20))

A field of an unnamed ROW type can be any of the following data types:

• A built-in type
• A collection type
• A distinct type
• Another ROW type
• An opaque type

Unnamed ROW types cannot be used in typed tables or in type inheritance hierarchies. For more information on unnamed ROW types, see the IBM Informix: Guide to SQL Syntax and the IBM Informix: Database Design and Implementation Guide.

Creating Unnamed ROW Types

You can create an unnamed ROW type in several ways:

• You can declare an unnamed ROW type using the ROW keyword. Each field in a ROW can have a different field type. To specify the field type, use the following syntax:

  ROW(field_name field_type, ...)

  The field_name must conform to the rules for SQL identifiers. (See the Identifier section in the IBM Informix: Guide to SQL Syntax.)

• To generate an unnamed ROW type, use the ROW keyword as a constructor with a series of values. A corresponding unnamed ROW type is created, using the default data types of the specified values.

  For example, the following declaration:

  ROW(1, 'abc', 5.30)

  defines this unnamed ROW data type:

  ROW (x INTEGER, y VARCHAR, z DECIMAL)

• You can create an unnamed ROW type by an implicit or explicit cast from a named ROW type or from another unnamed ROW type.
• The rows of any table (except a table defined on a named ROW type) are unnamed ROW types.

No more than 195 columns of the same table can be unnamed ROW types.

Inserting Values into Unnamed ROW Type Columns

When you specify field values for an unnamed ROW type, list the field values after the constructor and between parentheses. For example, suppose you have an unnamed ROW-type column. The following INSERT statement adds one group of field values to this ROW column:

INSERT INTO table1 VALUES (ROW(4, 'abc'))

You can specify a ROW column in the IN predicate in the WHERE clause of a SELECT statement to search for matching ROW values. For more information, see the Condition section in the IBM Informix: Guide to SQL Syntax.