Rules and Restrictions for Derived Tables - Teradata Database

Rules and Restrictions for Derived Tables

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 any of these SQL syntactical elements in a derived table:

ORDER BY

WITH

WITH … BY

Recursion

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 parallel to, and consistent with, the rules for creating a view.

The following query fails because 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.