Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
Other syntax segmentsThese topics describe syntax segments, which are language elements, such as database object names or optimizer directives, that appear as a subdiagram reference in the syntax diagrams of some SQL or SPL statements.
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.
Accessing a Nested Collection

Accessing a Nested Collection

If the element of the collection is itself a complex type (collection or row type), the collection is a nested collection. For example, suppose the collection variable, a_set, is a nested collection that is defined as follows:

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(list(integer not null)) a_set;
   client collection list(integer not null) a_list;
   int an_int;
EXEC SQL END DECLARE SECTION;

To access the elements (or fields) of a nested collection, use a collection or row variable that matches the element type (a_list and an_int in the preceding code fragment) and a Select cursor.