Select from a row variable
The SELECT statement and the collection-derived table clause allow you to select a particular field or group of fields in the row variable.
The INTO clause identifies the host variables that hold the field values selected from the row-type variable. The data type of the host variable in the INTO clause must be compatible with the field type.
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);
- 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.
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);
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);
The application can use dot notation to access fields of a nested row when a SELECT statement accesses a database column. For more information, see Select fields of a row column.