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


Collection-Derived Table

         (1)      
|--TABLE------(------------------------------------------------->

>--+-collection_expr)-+-----------+-+--------------------------+-+--|
   |                  +-AS--alias-+ |    .-,--------------.    | |   
   |                  '-alias-----' |    V                |    | |   
   |                                '-(----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 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 Informix ESQL/C row variable that holds the collection-derived table	Must have been declared in the Informix 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.