ROW( expression [, expression ... ] )

ROW( single-row-query-expression )

Field value

All fields are initialized to NULL, and remain NULL until a value is explicitly or implicitly placed within a particular field.

ROW types cannot be specified in the outermost SELECT list of a view definition, or in a top-level SELECT block or query expression that is returned to the client. ROW types cannot be stored as columns in a base or temporary table.

A row type can contain arbitrary levels of nesting, resulting in complex structured types. Each row sub-type is given a name, and these names can be referenced by using dot-notation to reference particular values. For example:

DECLARE sample ROW( x INT, w ROW( y INT, z INT ) );
SET Sample = ROW( 3, ROW( 6,7 ) );
SELECT (Sample).w.y FROM dummy;

Dot-notation permits other expressions in the same or nested query block to refer to all or portions of a row type for comparison or initialization of other ROW types.

When referring to fields within columns with a qualified name, surround column names with parentheses. In the following example, the myrowcolumn column is qualified by the name of its derived table:

SELECT ( myderivedtable.myrowcolumn ).id FROM
 (SELECT ROW( id, name ) 
         FROM GROUPO.Product )
  AS myderivedtable( myrowcolumn );

• Composite data types
• Comparisons of composite types
• CAST function [Data type conversion]

The following statement illustrates how to construct a ROW type that contains structure types of product information for each product in a Products table.

SELECT ROW( ID, NAME, DESCRIPTION ) AS pInfo FROM GROUPO.Products;

The pInfo row type has elements named ID, NAME, and DESCRIPTION, which are borrowed from the attribute names from the Products table.

The following statement illustrates how to use the CAST function to assign explicit names to an implicit ROW type:

SELECT CAST( ROW( 9, 'Tee Shirt', 'My tee shirt' )
    AS ROW(ProductID INTEGER, 
        ProductName CHAR(25),
        ProductDescription CHAR(35)
    )
    AS pInfo FROM dummy;

SQL expressions that use this query result as input can reference the components of the pInfo row by dotted expressions on their names. ROW types can also be constructed with a single-row query expression.

The following statement illustrates an alternative way of constructing a ROW:

SELECT ROW( SELECT Name, Quantity FROM Products WHERE ID = 300 );

The following statement illustrates how to set a field, last_name, in a ROW, student, by name:

SET student.last_name = 'Johnson';