Collection Subqueries

A collection subquery enables users to construct a collection expression from a subquery expression. A collection subquery uses the MULTISET keyword immediately before the subquery to convert the values returned into a MULTISET collection. However, when you use the MULTISET keyword before a subquery expression, the database server does not change the rows of the underlying table but only modifies a copy of the table rows. For example, if a collection subquery is passed to a user-defined routine that modifies the collection, then a copy of the collection is modified but not the underlying table.

A collection subquery is an expression that can take either of the following forms:

MULTISET(SELECT expression1, expression2... FROM tab_name...)

or

MULTISET(SELECT ITEM expression FROM tab_name...)

Omitting the Item Keyword in a Collection Subquery

If you omit the ITEM keyword in the collection subquery expression, the collection subquery is a MULTISET whose element type is always an unnamed ROW type. The fields of the unnamed ROW type match the expressions specified in the projection list of the subquery.

Suppose you create the following table that contains a column of type MULTISET:

CREATE TABLE tab2 
(
   id_num INT, 
   ms_col MULTISET(ROW(a INT) NOT NULL)
)

Figure 321 shows how you might use a collection subquery in a WHERE clause to convert the rows of INT values that the subquery returns to a collection of type MULTISET. In this example, the database server returns rows when the ms_col column of tab2 is equal to the result of the collection subquery expression.

SELECT id_num FROM tab2 
WHERE ms_col = (MULTISET(SELECT int_col FROM tab1))

Figure 321 omits the ITEM keyword in the collection subquery, so the INT values the subquery returns are of type MULTISET (ROW(a INT) NOT NULL) that matches the data type of the ms_col column of tab2.

Specifying the ITEM Keyword in a Collection Subquery

When the projection list of the subquery contains a single expression, you can preface the projection list of the subquery with the ITEM keyword to specify that the element type of the MULTISET matches the data type of the subquery result. In other words, when you include the ITEM keyword, the database server does not put a row wrapper around the projection list. For example, if the subquery (that immediately follows the MULTISET keyword) returns INT values, the collection subquery is of type MULTISET(INT NOT NULL).

Suppose you create a function int_func() that accepts an argument of type MULTISET(INT NOT NULL). Figure 322 shows a collection subquery that converts rows of INT values to a MULTISET and uses the collection subquery as an argument in the function int_func().

EXECUTE FUNCTION int_func(MULTISET(SELECT ITEM int_col
FROM tab1
   WHERE int_col BETWEEN 1 AND 10))

Figure 322 includes the ITEM keyword in the subquery, so the int_col values that the query returns are converted to a collection of type MULTISET (INT NOT NULL). Without the ITEM keyword, the collection subquery would return a collection of type MULTISET (ROW(a INT) NOT NULL).

For more information about syntax and restrictions for collection subqueries, see the IBM Informix: Guide to SQL Syntax.