15.00 - Using Subqueries To Customize An AS Clause - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Using Subqueries To Customize An AS Clause

Subqueries are a powerful means for customizing the definition and data for a target table by using a query expression to select source columns for the target table definition.

If you use a subquery to copy the source table columns and, optionally, their collected statistics, the system does not copy their attributes from the source table to the target table unless you also specify 1 or more column descriptors, in which case it copies only the column attributes you specify explicitly in the subquery to the target table.

If you do not specify any column descriptors, the system copies only the data types (and, optionally, the collected statistics) of the selected source columns or the resultant data types of any columns used in a subquery expression to the target table. It does not copy any attributes such as FORMAT, DEFAULT, COMPRESS, and so on unless you specify them explicitly.

A column descriptor is a target table column name followed by a specification of 1 or more column attributes. If you specify column descriptors in the definition for the target table, then the column attributes they specify replace any contradictory attributes of the selected source column.

Note that the system also copies any source column attributes from the selected columns that are not specified explicitly in a subquery column descriptor to the target table. See SQL Data Definition Language for an example.

Note the following things about this result that occur because the source was derived from a subquery rather than from a base table definition.

  • Teradata Database did not copy the table‑level attributes of table t to table t1.
  • Teradata Database did not copy the indexes defined for table t to table t1.
  • Because no explicit primary index was specified, the system selected the first valid column in t1, column a, to be its non‑unique primary index.
  • There are several workarounds for the limitations of the subquery form of CREATE TABLE … AS.

  • Use the subquery form, but specify the indexes and other table column descriptors explicitly for the target table.
  • Use the non‑subquery form of CREATE TABLE … AS.
  • Use the subquery form, then use ALTER TABLE (see “ALTER TABLE (Basic Table Parameters)” on page 31) to add the desired attributes to the new table.
  • Either use the WITH DATA option for CREATE TABLE … AS (see “Specific Rules For AS … WITH NO DATA That Uses A Subquery To Define The Source Table Column And Index Set” on page 669) or create a new table, then populate it with the data from the original table using an INSERT … SELECT request (see SQL Data Manipulation Language).
  • The following list itemizes important properties and rules for use of subqueries expressed in an AS clause.

  • Most valid subquery can be specified in the AS clause.
  • Any restrictions on valid subqueries also apply for the subquery forms of the AS clause. For example, the following restrictions apply.
  • You cannot specify an ORDER BY clause.
  • The WHERE, HAVING, QUALIFY, or ON clause of any condition specified within a recursive view definition cannot specify a SAMPLE clause within a predicate subquery.
  • The following table lists the rules for AS and NAMED clauses.
  •  

    IF …

    THEN …

    both AS and NAMED clauses are specified

    the AS clause takes precedence and the column names for the target table are derived from the names it specifies.

    no column names are specified

    names specified for expressions are used to name the derived columns of the target table.

  • The following restrictions apply to subqueries.
  •  

    IF a subquery contains …

    THEN …

    only column names

    column names for the new table default to the existing names of the selected columns if you do not specify different names for them.

    a join expression

    duplicate column names from the joined tables must be differentiated by fully qualified names.

    Otherwise, the subquery is not valid and the request aborts.

  • The following copy traits are defined for the subquery forms of the AS clause.
  • No column-level attributes are copied except for data type.
  • No table-level attributes, including indexes, are copied.
  • The following user or database default attributes are used for the target table unless you specify explicit values for them in the CREATE TABLE request.
  • The standard user and database defaults are defined in the following table.

     

    Table Option

    Permanent Table Defaults

    Global Temporary Table Defaults

    Volatile Table Defaults

    Fallback

    None

    None

    None

    Permanent journaling

    No Before

    No After

    Not permitted

    Not permitted

    Transaction journaling

    None

    Log

    Log

    Referential integrity constraint

    None

    Not permitted

    Not permitted

    CHECK or BETWEEN constraints

    None

    None

    Not permitted

    COMPRESS column

    None

    None

    Not permitted

    DEFAULT and TITLE clauses

    None

    None

    Not permitted

    Named Index

    None

    None

    Not permitted

    FREESPACE or DBSIZE

    User or Database default

    User or Database default

    User or Database default

    ON COMMIT

    Not applicable

    Delete

    Delete