Collection-Derived Table

A collection-derived table is a virtual table in which the values in the rows of the table are equivalent to elements of a collection. Use this segment where you see a reference to Collection-Derived Table in a syntax diagram. This syntax is an extension to the ANSI/ISO standard for SQL.

Syntax

(1)
Collection-Derived Table

1   TABLE1 (
1 collection_expr)
2.1  AS alias
2.1 alias
1? ( + , derived_column )
1 
2.1 23 collection_var
2.1 3 row_var
1 )
Notes:
  • 1 HCL OneDB™ extension
  • 2 Stored Procedure Language
  • 3 ESQL/C

Element Description Restrictions Syntax
alias Temporary name for a collection-derived table whose scope is a SELECT statement. The default is implementation dependent. If potentially ambiguous, you must precede alias with the AS keyword. See Aliases for Tables or Views. Identifier
collection_expr Any expression that evaluates to the elements of a single collection See Restrictions with the Collection-Expression Format. Expression
collection_var, row_var Name of a typed or untyped collection variable, or row variable that holds the collection-derived table Must have been declared in the program or (for collection_var) in an SPL routine See the HCL OneDB ESQL/C Programmer's Manual or DEFINE.
derived _column Temporary name for a derived column in a table If the underlying collection is not of a ROW data type, you can specify only one derived-column name Identifier

Usage

A collection-derived table can appear where a table name is valid in the UPDATE statement, in the FROM clause of the SELECT or DELETE statement, or in the INTO clause of an INSERT statement.

Use the collection-derived-table segment to accomplish these tasks:
  • Access the elements of a collection as you would the rows of a table.
  • Specify a collection variable to access, instead of a table name.
  • Specify an ESQL/C row variable to access, instead of a table name.

The TABLE keyword converts a collection into a virtual table. You can use the collection expression format to query a collection column, or you can use the collection variable or row variable format to manipulate the data in a collection column.