Table Expressions in the FROM Clause

HCL OneDB™ supports ANSI/ISO standard syntax for table expressions in the FROM clause of SELECT queries and subqueries as a substitute for the HCL OneDB-extension collection-derived table syntax. The keywords TABLE and MULTISET were required in version 10.00 and in earlier releases. These extensions to the ANSI/ISO standard for SQL are supported but no longer required for collection-derived table specifications in the FROM clause of SELECT statements.

The following two queries return the same result set, but only the second query complies with the ANSI/ISO standard:

SELECT * FROM TABLE(MULTISET(SELECT col1 FROM tab1 WHERE col1 = 100))
   AS vtab(c1),
   (SELECT col1 FROM tab1 WHERE col1 = 10) AS vtab1(vc1) ORDER BY c1;

SELECT * FROM (SELECT col1 FROM tab1 WHERE col1 = 100) AS vtab(c1),
   (SELECT col1 FROM tab1 WHERE col1 = 10) AS vtab1(vc1) 
      ORDER BY c1;

The same SELECT statement can combine instances of both the HCL OneDB-extension and ANSI/ISO syntax for derived tables:

SELECT * FROM (SELECT col1 FROM tab1 WHERE col1 = 100) AS vtab(c1), 
   TABLE(MULTISET(SELECT col1 FROM tab1 WHERE col1 = 10)) AS vtab1(vc1)  
      ORDER BY c1;

The subquery must be delimited by parentheses in both formats, but the outer set of parentheses ( ( ) ) that immediately follows the TABLE keyword and encloses the MULTISET collection subquery specification is an extension to the ANSI/ISO syntax. This ANSI/ISO syntax is valid only in the FROM clause of the SELECT statement. You cannot omit these keywords and parentheses from a collection subquery specification in any other context.