Select from a row variable

For example, the following figure contains a code fragment that puts the value of the width field (in the row variable myrect) 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 (one that contains a collection-derived table clause) has the following restrictions:

• No expressions are allowed in the select list.
• The select list must be an asterisk (*) if the row contains elements of opaque, distinct, or built-in data types.
• Column names in the select list must be simple column names.

  These columns cannot use the database@server:table.column syntax.

• The select list cannot use dot notation to access fields of the row.
• The following SELECT clauses are not allowed: GROUP BY, HAVING, INTO TEMP, ORDER BY, and WHERE.
• The FROM clause has no provisions to do a join.
• Row-type columns cannot be specified in a comparison condition in a WHERE clause.

If the row variable is a nested row, a SELECT statement cannot use dot notation to access the fields of the inner row. Instead, you must declare a row variable for each row type. The code fragment in the following figure shows how to access the fields of the inner row in the nested_row host variable.

EXEC SQL BEGIN DECLARE SECTION;
   row (a int, b row(x int, y int)) nested_row;
   row (x int, y int) inner_row;
   integer x_var, y_var;
EXEC SQL END DECLARE SECTION;

EXEC SQL select row_col into :nested_row from tab_row
   where a = 7;
EXEC SQL select b into :inner_row 
   from table(:nested_row);
EXEC SQL select x, y into :x_var, :y_var 
   from table(:inner_row);

The following SELECT statement is not valid to access the x and y fields of the nested_row variable because it uses dot notation:

EXEC SQL select row_col into :nested_row from tab_row
EXEC SQL select b.x, b.y    /* invalid syntax */
   into :x_var, :y_var from table(:nested_row);