Supported Syntax
Column Override List
Ingres and Microsoft SQL Server support the following syntax:
Without column override list:
SELECT … FROM (SELECT col1 [AS c1][, col2 [AS c2], …] FROM tabname) corr_tabname
With column override list:
SELECT … FROM (SELECT col1 [AS c1][, col2 [AS c2], …] FROM tabname) corr_tabname (c1, c2…)
Oracle only supports derived table syntax without the column override list in parentheses:
SELECT … FROM ( SELECT col1 [AS c1][, col2 [AS c2], …] FROM tabname) corr_tabname
Enterprise Access supports both syntaxes and allows the underlying DBMS to determine whether the syntax is acceptable. If the host DBMS returns an error, both the Enterprise Access and the host-specific error messages are sent back to the application.
Parentheses in Tablename Area Without a Subselect
The following statement uses parentheses in the tablename area:
select user from (iidbconstants);
If iidbconstants is a derived table, the parentheses must be removed:
select user from iidbconstants;
Correlation Names
The following statement contains no correlation name:
SELECT xname FROM
(SELECT user_name AS xname FROM iidbconstants where user_name=USER);
For use with a derived table, you must add the correlation name ("x"):
SELECT xname FROM
(SELECT user_name AS xname FROM iidbconstants where user_name=USER) x;
Subquery in FROM clause Has ORDER BY Clause
With derived tables, ORDER BY cannot be used in the FROM clause:
SELECT xname FROM
(SELECT user_name AS xname FROM iidbconstants order by user_name) x;
Remove the order by clause:
SELECT xname FROM
(SELECT user_name AS xname FROM iidbconstants) x;
Last modified date: 08/22/2022