Informix Guide to SQL: Tutorial Composing Advanced SELECT Statements

The database server provides the following SQL features to handle collection expressions:

• 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 Informix Guide to Database Design and Implementation.

• 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.

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...)

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)
)

Query 5-30 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))

Query 5-30 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.

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). Query 5-31 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))

Query 5-31 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 Informix Guide to SQL: Syntax.

A collection-derived table enables you to handle the elements of a collection expression as rows in a virtual table. Use the TABLE keyword in the FROM clause of a SELECT statement to create a collection-derived table. The database server supports collection-derived tables in SELECT, INSERT, UPDATE, and DELETE statements.

Query 5-32 uses a collection-derived table named c_table to access elements from the sales column of the sales_rep table in the superstores_demo database. The sales column is a collection of an unnamed row type whose two fields, month and amount, store sales data. Query 5-32 returns an element for sales.amount when sales.month equals 98-03. Because the inner select is itself an expression, it cannot return more than one column value per iteration of the outer query. The outer query specifies how many rows of the sales_rep table are evaluated.

SELECT (SELECT c_table.amount FROM TABLE (sales_rep.sales) 
c_table WHERE c_table.month = '98-03') 
FROM sales_rep 

 






 Query Result 5-32 







 

Query 5-33 uses a collection-derived table to access elements from the sales collection column where the rep_num column equals 102. With a collection-derived table, you can specify aliases for the table and columns. If no table name is specified for a collection-derived table, the database server creates one automatically. This example specifies the derived column list s_month and s_amount for the collection-derived table c_table.

SELECT * FROM TABLE((SELECT sales FROM sales_rep 
WHERE sales_rep.rep_num = 102)) c_table(s_month, s_amount)

 






 Query Result 5-33 







 

Query 5-34 creates a collection-derived table but does not specify a derived table or derived column names. Query 5-34 returns the same result as Query 5-33 except the derived columns assume the default field names of the sales column in the sales_rep table.

SELECT * FROM TABLE((SELECT sales FROM sales_rep 
WHERE sales_rep.rep_num = 102))

 






 Query Result 5-34 







 


Important: A collection-derived table is read only, so it cannot be the target table of INSERT, UPDATE, or DELETE statements or the underlying table of an updateable cursor or view.

For a complete description of the syntax and restrictions on collection-derived tables, see the Informix Guide to SQL: Syntax.