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

• ORDER BY
• WITH ... BY

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);