Collection Subquery

You can use a Collection Subquery to create a MULTISET collection from the results of a subquery. This syntax is an extension to the ANSI/ISO standard for SQL.

Syntax

(1)
Collection Subquery

1 1 MULTISET  (
2.1 subquery
2.1  SELECT ITEM singleton_select
1 )
Notes:
  • 1 HCL OneDB™ extension

Element Description Restrictions Syntax
singleton _select Subquery returning exactly one row Subquery cannot repeat the SELECT keyword, nor include the ORDER BY clause SELECT statement
subquery Embedded query Cannot contain the ORDER BY clause SELECT statement

Usage

The MULTISET and SELECT ITEM keywords have the following significance:
  • MULTISET specifies a collection of elements that can contain duplicate values, but that has no specific order of elements.
  • SELECT ITEM supports only one expression in the projection list. You cannot repeat the SELECT keyword in the singleton subquery.
You can use a collection subquery in the following contexts:
  • The Projection clause and WHERE clause of the SELECT statement
  • The VALUES clause of the INSERT statement
  • The SET clause of the UPDATE statement
  • Wherever you can use a collection expression (that is, any expression that evaluates to a single collection)
  • As an argument passed to a user-defined routine
The following restrictions apply to a collection subquery:
  • The Projection clause cannot contain duplicate column (field) names.
  • It cannot contain aliases for table names. (But it can use aliases for column (field) names, as in some of the examples that follow. )
  • It is read-only.
  • It cannot be opened twice.
  • It cannot contain NULL values.
  • It cannot contain syntax that attempts to seek within the subquery.
A collection subquery returns a multiset of unnamed ROW data types. The fields of this ROW type are elements in the projection list of the subquery. Examples that follow access the tables and the ROW types that these statements define: 
CREATE ROW TYPE rt1 (a INT);
CREATE ROW TYPE rt2 (x int, y rt1);
CREATE TABLE tab1 (col1 rt1, col2 rt2);
CREATE TABLE tab2 OF TYPE rt1;
CREATE TABLE tab3 (a ROW(x INT));
The following examples of collection subqueries return the MULTISET collections that are listed to the right of the subquery.
Collection Subquery Resulting Collections
MULTISET (SELECT * FROM tab1)... MULTISET(ROW(col1 rt1, col2 rt2))
MULTISET (SELECT col2.y FROM tab1)... MULTISET(ROW(y rt1))
MULTISET (SELECT * FROM tab2)... MULTISET(ROW(a int))
MULTISET(SELECT p FROM tab2 p)... MULTISET(ROW(p rt1))
MULTISET (SELECT * FROM tab3)... MULTISET(ROW(a ROW(x int)))
The following is another collection subquery: 
SELECT f(MULTISET(SELECT * FROM tab1 WHERE tab1.x = t.y))
   FROM t WHERE t.name = 'john doe';
The following collection subquery includes the UNION operator: 
SELECT f(MULTISET(SELECT id FROM tab1 
UNION
SELECT id FROM tab2 WHERE tab2.id2 = tab3.id3)) FROM tab3;