17.05 - Rules and Restrictions for Derived Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
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.