Support for Derived Tables
Derived table support was added to Enterprise Access 3.0 in patch 14798. A derived table is an intermediate table, dynamically produced as the result of a SELECT subquery in the FROM clause of a SELECT query. Enterprise Access now supports this feature.
Derived tables let you create or simplify complex queries. Useful in data warehousing applications, derived tables provide a way to isolate complex portions of query syntax from the rest of a query. A derived table is specified in the coding of a SELECT in the FROM clause of a SELECT statement. The syntax is as follows:
SELECT expression FROM (SELECT expression
[AS result_column]{, expression [AS result_column]}
FROM table_name) correlation_name
[(override_column {, override_column})]
For example:
select relid, x.attname
from (select attrelid, attrelidx, attname,
attfrml from iiattribute) x, iirelation
where reltid = attrelid and reltidx = x.attrelidx
A subquery in a FROM clause cannot use ORDER BY:
SELECT xname FROM
(SELECT user_name AS xname FROM iidbconstants order by
user_name) x;
In this case, you must remove the ORDER BY:
SELECT xname FROM
(SELECT user_name AS xname FROM iidbconstants) x;
The derived table behaves like an inline view; the rows of the result set of the derived table are read and joined to the rest of the query. Some complex queries cannot be implemented without using either predefined views or derived tables. The derived table approach is more concise than predefined views and avoids having to define persistent objects, such as views, that may be used for a single query only.
For more information about derived tables and their syntax, see the Ingres SQL Reference Guide and the Ingres OpenSQL Reference Guide. For more information about DBMS limitations with derived tables, see the Enterprise Access Developing Portable Applications Guide.