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.
Figure 323 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. Figure 323 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
(expression) $47.22 $53.22
Figure 325 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)
s_month s_amount 1998-03 $53.22 1998-04 $18.22
Figure 327 creates a collection-derived table but does not specify a derived table or derived column names. Figure 327 returns the same result as Figure 325 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))
month amount 1998-03 $53.22 1998-04 $18.22
For a complete description of the syntax and restrictions on collection-derived tables, see the IBM Informix: Guide to SQL Syntax.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]