Handle collections in SELECT statements
- Collection subquery
- A collection subquery takes a virtual table (the result of a subquery)
and converts it into a collection.
A collection subquery always returns a collection of type MULTISET. You can use a collection subquery to convert a Query result of relational data into a MULTISET collection. For information about the collection data types, see the HCL OneDB™ Database Design and Implementation Guide.
- Collection-derived table
- A collection-derived table takes a collection and converts it
into a virtual table.
Each element of the collection is constructed as a row in the collection-derived table. You can use a collection-derived table to access the individual elements of a collection.
The collection subquery and collection-derived table features represent inverse operations: the collection subquery converts row values from a relational table into a collection whereas the collection-derived table converts the elements of a collection into rows of a relational table.