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. Only Dynamic Server supports this syntax, which is an extension to the ANSI/ISO standard for SQL.

Syntax

Collection-Derived Table:
 
         (1)
|--TABLE-------(------------------------------------------------>
 
>--+-collection_expr)-+------------------+-+--------------------------+-+--|
   |                  |  (1)             | |    .-,--------------.    | |
   |                  +--------AS--alias-+ |    V                |    | |
   |                  '-alias------------' '-(----derived_column-+--)-' |
   |    (2)    (3)                                                      |
   '-+---------------collection_var-+--)--------------------------------'
     |  (3)                         |
     '--------row_var---------------'

Notes:

Informix extension
Stored Procedure Language
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 The AS Keyword.	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 an ESQL/C row variable that holds the collection-derived table	Must have been declared in an ESQL/C program or (for collection_var) in an SPL routine	See the IBM Informix 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.