Select one element

The SELECT statement and the collection-derived table clause allow you to select one element into a collection.

The INTO clause identifies the variable in which to store 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 the element type of the collection.

The following code fragment selects only one element from the set_col column (see Sample tables with collection columns) with a typed collection host variable called a_set:

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(integer not null) a_set;
   int an_element, set_size;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :a_set;
EXEC SQL select set_col, cardinality(set_col) 
   into :a_set, :set_size from tab_set
   where id_col = 3;
if (set_size == 1)
   EXEC SQL select * into :an_element from table(:a_set);

Important: Use this form of the SELECT statement when you are sure that the SELECT returns only one element. returns an error if the SELECT returns more than one element. If you do not know the number of elements in the set or if you know that the set contains more than one element, use a select cursor to access the elements.

For more information about how to use a select cursor, see Selecting more than one element.

If the element of the collection is itself a complex type (collection or row type), the collection is a nested collection. For information about how to use a cursor to select elements from a nested collection, see Select values from a nested collection. The following section describes how to use a row variable to select a row element from a collection.