Selecting from a Collection Variable (IDS)

The SELECT statement in conjunction with the Collection-Derived-Table segment allows you to select elements from a collection variable.

The Collection-Derived-Table segment identifies the collection variable from which to select the elements. (See Collection-Derived Table.)

Using Collection Variables with SELECT

To modify the contents of a column of a collection data type, you can use the SELECT statement with a collection variable in various ways:

• You can select the contents (if any) of a collection column into a collection variable.

  You can assign the data type of the column to a collection variable of type COLLECTION (that is, an untyped collection variable).

• You can select the contents from a collection variable to determine the data that you might want to update.
• You can select the contents from a collection variable INTO another variable in order to update certain collection elements.

  The INTO clause identifies the variable for the element value that is selected from the collection variable. The data type of the host variable in the INTO clause must be compatible with that of the corresponding collection element.

• You can use a collection cursor to select one or more elements from an ESQL/C collection variable.

  For more information, including restrictions on the SELECT statement, see Associating a Cursor with a Prepared Statement.

• You can use a collection cursor to select one or more elements from an SPL collection variable.

  For more information, including restrictions on the SELECT statement, see Using a SELECT ... INTO Statement.

When one of the tables to be joined is a collection, the FROM clause cannot specify a join. This restriction applies when the collection variable holds your collection-derived table. See also Collection-Derived Table. and the INSERT, UPDATE, and DELETE statement descriptions in this chapter.