Rules and Restrictions for Derived Tables - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
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;

    Result:

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