Derived Tables - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Derived Tables

The derived table option enables the FROM clause of a seed statement to specify a spool made up of selected data from an underlying table set. The derived table acts like a viewed table.

 

Syntax Element …

Specifies …

(subquery)

nested SELECT statements.

SELECT AND CONSUME statements cannot be used in a subquery.

You can specify NORMALIZE in a subquery.

AS

an optional introduction to derived_table_name.

derived_table_name

the name of the derived table.

column_name

the column name. This field is for the column name only. Do not use forms such as Tablename.Columnname or Databasename.Tablename.Columnname.

Columns with a UDT type are valid with some exceptions. See “Specifying UDTs in an SQL Request” on page 19 for the specific exceptions.

WHERE

an introduction to the search condition in the seed statement.

search_condition

a conditional search expression that must be satisfied by the row or rows returned by the seed statement.

If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.

GROUP BY

an introduction to the specification of how to group result rows.

ordinary_group_set

a column expression by which the rows returned by the seed statement are grouped.

The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type.

ordinary_grouping_set falls into three general categories:

  • column_name
  • column_position
  • column_expression
  • See “Ordinary Grouping Set Expressions” on page 128 for definitions.

    empty_grouping_set

    a contiguous LEFT PARENTHESIS, RIGHT PARENTHESIS pair with no argument. This syntax is used to request a grand total of the computed group totals.

    rollup_list

    a ROLLUP expression that reports result rows in a single dimension with one or more levels of detail.

    The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type.

    cube_list

    a CUBE expression that reports result rows in multiple dimensions with one or more levels of detail.

    The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type.

    grouping_sets_
    specification

    a GROUPING SETS expression that reports result rows in one of two ways:

  • As a single dimension, but without a full ROLLUP.
  • As multiple dimensions, but without a full CUBE.
  • HAVING

    an introduction to the conditional clause in the SELECT statement.

    search_condition

    one or more conditional expressions that must be satisfied by the result rows.

    If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.

    QUALIFY

    an introduction to a conditional clause that, similar to HAVING, further filters rows from a WHERE clause. The major difference between QUALIFY and HAVING is that QUALIFY filtering is based on the result of performing various ordered analytical functions on the data.

    search_condition

    one or more conditional expressions that must be satisfied by the result rows.

    If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.

    ORDER BY

    the order in which result rows are to be sorted.

    expression

    an expression in the SELECT expression list of the seed statement, either by name or by means of a constant that specifies the numeric position of the expression in the expression list.

    column_name

    the names of columns used in the ORDER BY clause in the SELECT statement. These can be ascending or descending. See “SELECT” on page 8.

    column_name_alias

    a column name alias specified in the select expression list of the query for the column on which the result rows are to be sorted.

    If you specify a column_name_alias to sort by, then that alias cannot match the name of any column that is defined in the table definition for any table referenced in the FROM clause of the query whether that column is specified in the select list or not. This does not work because the system always references the underlying physical column having the name rather than the column that you attempt to reference using that same name as its alias.

    If you attempt to specify such a column_name_alias, the system aborts the request and returns an error.

    The workaround for this is to specify the sort column by its column_position value within the select list for the query. See “Rules and Restrictions for the ORDER BY Clause” on page 204.

    column_position

    the numeric position of the columns specified by the ORDER BY clause. These can be ascending or descending.

    ASC

    Results are to be ordered in ascending sort order.

    If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

    The default order is ASC.

    DESC

    Results are to be ordered in descending sort order.

    If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

    table_name

    the name of the named query or the name of a table or view.

    table_name.* in the select list can define the table from which rows are to be returned when two or more tables are referenced in the FROM clause.

    FROM

    an introduction to the named query and one or more tables or views from which expression is to be derived.

    The FROM clause in a recursive statement cannot specify the TABLE option.