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 subquery syntax. The keywords TABLE and MULTISET are required in version 10.00 and in earlier releases. These extensions to the ANSI/ISO standard for SQL are supported but are no longer required for collection subqueries in the FROM clause of SELECT statements.

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;

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 collection 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.