Collection-derived tables

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.

The following query 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. The query 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.

Figure 1: Query

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

Figure 2: Query result

(expression)

$47.22 
$53.22

The following query 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.

Figure 3: Query

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

Figure 4: Query result

s_month         s_amount 

1998-03           $53.22
1998-04           $18.22

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

Figure 5: Query

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

Figure 6: Query result

month             amount 

1998-03           $53.22
1998-04           $18.22

Restriction: 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 updatable cursor or view.

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