ISO-compliant syntax for collection derived tables

The query examples in the topic Collection-derived tables specify collection-derived tables by using the TABLE keyword followed by a SELECT statement enclosed within double parentheses. This syntax is the HCL® OneDB® extension to the ANSI/ISO standard for the SQL language.

In the FROM clause of the SELECT statement, however, and only in that context, you can instead use syntax that complies with the ANSI/ISO standard for SQL by specifying a subquery, without the TABLE keyword or the nested parentheses, to define a collection-derived table.

The following example is logically equivalent to Query, and specifies the derived column list s_month and s_amount for the collection-derived table c_table.

Figure 1: Query

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

Figure 2: Query result

s_month         s_amount 

1998-03           $53.22
1998-04           $18.22

As in the HCL OneDB extension syntax, declaring names for the derived table or for its columns is optional, rather than required. The following query uses ANSI/ISO-compliant syntax for a derived table in the FROM clause of the outer query, and produces the same results as Query:

Figure 3: Query

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

Figure 4: Query result

month             amount 

1998-03           $53.22
1998-04           $18.22