Selecting from a Row Variable (ESQL/C)

The SELECT statement can include the Collection-Derived Table segment to select one or more fields from a row variable.

About this task

The Collection-Derived Table segment identifies the row variable from which to select the fields. For more information, see Collection-Derived Table.

To select fields:

Procedure

  1. Create a row variable in your program.
  2. Optionally, fill the row variable with field values.
    You can select a ROW-type column into the row variable with the SELECT statement (without the Collection-Derived Table segment). Alternatively, you can insert field values into the row variable with the UPDATE statement and the Collection-Derived Table segment.
  3. Select row fields from the row variable with the SELECT statement and the Collection-Derived Table segment.
  4. Once the row variable contains the correct field values, you can use the INSERT or UPDATE statement on a table or view name to save the contents of the row variable in a named or unnamed row column.

Results

The INTO clause can specify a host variable to hold a field value selected from the row variable.

The type of the host variable must be compatible with that of the field. For example, this code fragment puts the width field value into the rect_width host variable. 
EXEC SQL BEGIN DECLARE SECTION;
   ROW (x INT, y INT, length FLOAT, width FLOAT) myrect;
   double rect_width;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SELECT rect INTO :myrect FROM rectangles
   WHERE area = 200;
EXEC SQL SELECT width INTO :rect_width FROM table(:myrect);
The SELECT statement on a row variable has the following restrictions:
  • No expressions are allowed in the select list of the Projection clause.
  • ROW columns cannot be in a WHERE clause comparison condition.
  • The Projection clause must be an asterisk ( * ) if the row-type contains fields of opaque, distinct, or built-in data types.
  • Columns listed in the Projection clause can have only unqualified names. They cannot use the syntax database@server:table.column.
  • The following clauses are not allowed: GROUP BY, HAVING, INTO TEMP, ORDER BY, and WHERE.
  • The FROM clause has no provisions to do a join.

You can modify the row variable with the Collection-Derived Table segment of the UPDATE statements. (The INSERT and DELETE statements do not support a row variable in the Collection-Derived Table segment.)

The row variable stores the fields of the row. It has no intrinsic connection, however, with a database column. Once the row variable contains the correct field values, you must then save the variable into the ROW column with one of the following SQL statements:
  • To update the ROW column in the table with the row variable, use an UPDATE statement on a table or view name and specify the row variable in the SET clause. For more information, see Updating ROW-Type Columns.
  • To insert a row into a ROW column, use the INSERT statement on a table or view and specify the row variable in the VALUES clause. See Inserting Values into ROW-Type Columns.

For examples of how to use SPL row variables, see the HCL OneDB™ Guide to SQL: Tutorial. For information on using row variables, see the discussion of complex data types in the HCL OneDB ESQL/C Programmer's Manual.