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 Informix® 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 Informix 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);