SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-29
Using Rowset-Derived Tables in DML Statements
:array-name [,:array-name]...
specifies a set of host variable arrays. Each array-name can be used like a
column in the rowset-derived table. Each array-name can be any valid host
language identifier with a data type that corresponds to an SQL data type. Precede
each array-name with a colon (:) within an SQL statement.
KEY BY row-id
optionally identifies each tuple or row processed in the rowset-derived table during
the evaluation of the SQL statement. The row-id, if specified, must be the last
variable specification in the derived column list.
[AS] correlation
is the correlation name of the table reference and can be any SQL identifier.
column [,column]...
specifies the list of derived columns of the rowset-derived table that corresponds
one-to-one to the list :array-name [,:array-name]... of array names, with
the exception that the last column in the list must be the row-id, if specified.
Using Rowset-Derived Tables in DML
Statements
Use rowset-derived tables in DML statements:
Selecting From Rowset-Derived Tables
Use a rowset as input in a SELECT statement to improve performance. A single
execution of the statement is performed with the input rowset instead of multiple
executions of an equivalent statement with successive individual values for input.
Technique Description
Selecting From Rowset-Derived Tables
Single execution with an input rowset
instead of multiple executions with
individual input values.
Inserting Rows From Rowset-Derived Tables
Multiple rows are inserted by using a query
that retrieves values from a rowset-derived
table.
Updating Rows by Using Rowset-Derived
Tables
Multiple logical executions of an UPDATE
statement are performed by using a
subquery in the WHERE clause.
Deleting Rows by Using Rowset-Derived
Tables
Multiple logical executions of the DELETE
statement are performed by using a
subquery in the WHERE clause.