The rules and restrictions are:
- The semantics of derived tables and views are identical, as are their restrictions.
- The scope of a derived table is limited to the level of the SELECT statement calling the subquery.
- A unique table correlation name is required for each derived table you create within a statement.
- You cannot specify these options in a derived table:
- ORDER BY
- WITH … BY
- Fully-qualified column names are mandatory when you specify otherwise ambiguous column names in the select list of a subquery used to build a derived table.
This rule is consistent with the rules for creating a view.
In this query, the columns specified in the select list of the subquery that builds the derived table are not qualified:
SELECT * FROM (SELECT * FROM tab1 AS t1, tab2 AS t2 WHERE t1.col2 = t2.col3) AS derived_table; *** Failure 3515 Duplication of column COL1 in creating a Table, View, Macro or Trigger. Statement# 1, Info =95 *** Total elapsed time was 1 second.
The query is correctly written as follows:
SELECT * FROM (SELECT t1.col1, t1.col2, t1.col3, t2.col1, t2.col2, t2.col3 FROM tab1 AS t1, tab2 AS t2 WHERE t1.col2=t2.col3) AS derived_table (t1_col1, t1_col2, t1_col3, t2_col1, t2.col2, t2_col3);
- The subquery that defines the contents of a derived table cannot contain SELECT AND CONSUME statements.