17.05 - Rules and Restrictions for the EXPAND ON Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
The rules and restrictions are:
  • There are three different ways to expand rows:
    • By user-specified intervals such as 1 DAY, 3 MONTH, and so on.

      You must specify an interval literal in the EXPAND ON clause for this form of expansion. This type of expansion is called an interval expansion.

      An interval expansion can be useful for answering queries such as “compute the moving window average of inventory cost by week during the year 2010."

    • By user-specified anchored points in a time line.

      You must specify an anchored interval without specifying a PERIOD keyword for this form of expansion. Such expansion is called an anchor point expansion.

      An anchor point expansion produces a specific date or time point in the expanded rows, where the date or time points are the anchor points present in the input period being expanded.

      For example, BY ANCHOR MONTH_BEGIN returns multiple expanded rows, one for each beginning value of a month present in the input period. This form of expansion might be useful for answering queries such as “get the month end average inventory cost during the last quarter of the year 2010.”

    • By user-specified anchored time durations in a time line, referred to as anchor period expansion.

      You must specify an anchored interval with the PERIOD keyword for this form of expansion.

      An anchor PERIOD expansion produces rows whose period beginning bounds are always aligned to specific DateTime values derived from the anchor names.

      For example, BY ANCHOR PERIOD MONTH_BEGIN, BY ANCHOR PERIOD MONDAY, and so on.

      The PERIOD value of the row along with fixed durations enables weighted computations such as "compute the weekly weighted average of inventory cost."

  • You can use the EXPAND ON clause in query expressions, except for:
    • Subqueries in search conditions. See Specifying Subqueries in Search Conditions.

      For example, the EXPAND ON clause in the following statement is not valid because of the reference to the column named pd1 in employee:

         SELECT *
         FROM employee
         WHERE salary IN (SELECT salary
                         FROM salary_table
                          WHERE salary > 10000
                          EXPAND ON pd1);
    • A WITH clause is specified in the query expression. See WITH Clause.
    • An updatable cursor SELECT statement. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 .
    • A CREATE JOIN INDEX statement. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
    • The query expression is a SELECT AND CONSUME statement. See SELECT AND CONSUME.
  • The expand expression can be a column or column alias from the select list or a number that specifies the positional sequence of the column or expression in the select list that is to be expanded.

    If the specified column sequential position is not valid, for example, if the value is greater than the projected number of columns, the database returns an error.

    The specified column must have a data type of PERIOD.

    You must specify a table reference in a SELECT statement with an EXPAND ON clause.

  • You can specify an EXPAND ON clause in any of the following places:
    • Within a derived table.

      This includes cases where the derived table is contained within a subquery.

      See Example: Expansion Over a UNION Operator.

    • As part of the individual query expression of a SELECT statement that specifies set operators such as UNION, INTERSECT, or MINUS/EXCEPT.

      See Example: Expansion Over a UNION Operator.

    • As part of the SELECT specification in the following SQL DDL statements. See CREATE RECURSIVE VIEW/REPLACE RECURSIVE VIEW seed and the recursive statements of the view definition, CREATE TABLE … AS, and CREATE VIEW/REPLACE VIEW in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

      Note that a view that contains an EXPAND ON clause in its definition is not updatable.

    • As part of the SELECT specification in an INSERT … SELECT statement. See INSERT/INSERT … SELECT.
  • You cannot specify an EXPAND ON clause in the following situations:
    • Anywhere in a SELECT AND CONSUME statement. See SELECT AND CONSUME.
    • Anywhere in an updatable cursor. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 .
    • Anywhere in a subquery used as a search condition.
    • Anywhere in a SELECT statement that specifies a TOP n operator. See TOP Clause.

      You can work around this restriction by specifying the TOP n operator within a derived table and then specifying the EXPAND ON clause in the outer query.

    • Anywhere in a SELECT statement that specifies a SAMPLE clause. See SAMPLE Clause.
    • Anywhere in a SELECT statement that specifies a WITH clause. See WITH Clause.
    • Anywhere in a table function.
    • As part of the SELECT specification in a CREATE JOIN INDEX statement. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
    • As part of the SELECT specification in an INSERT... SELECT statement that includes the LOCAL ORDER BY option. This restriction also applies to an INSERT... SELECT statement included in a CREATE TRIGGER, REPLACE TRIGGER, CREATE MACRO, REPLACE MACRO, CREATE PROCEDURE, or REPLACE PROCEDURE statement.
    • As part of the SELECT specification in an INSERT... SELECT statement where the target table is defined with NO PRIMARY INDEX. This restriction also applies to an INSERT... SELECT statement included in a CREATE TRIGGER, REPLACE TRIGGER, CREATE MACRO, REPLACE MACRO, CREATE PROCEDURE, or REPLACE PROCEDURE statement.
  • All of the operations in a query expression except ORDER BY are performed before any rows are expanded.

    The ORDER BY operation is then performed on the expanded rows if you specify an expanded column in the ORDER BY clause.

  • If you do not specify a BY expansion_interval clause, the expanding interval defaults to the granularity of the element type of the PERIOD value for the column.

    The expansion defaults to the rules for interval literals described in Rules and Restrictions for Interval Expansion.

  • To limit the number of rows being expanded, specify a PERIOD expression in the FOR clause, where the PERIOD expression represents the period of interest.

    The data type of the PERIOD expression must be compatible with the PERIOD data type of expanded_column_name.

    If the specified period is not null and does not overlap with the row, then the row is not eligible for expansion.

  • The database expands rows based on the interval you specify.

    The interval can either be an interval constant, or an anchored interval derived from the anchor name such as any day of a week, the MONTH_BEGIN keyword, or the MONTH_END keyword.

  • An EXPAND ON clause specification produces one value-equivalent row for each time granule derived from the specified expansion_interval. Rows are said to be value-equivalent if the values in all columns except for their expanded columns are identical.
  • If you specify a FOR clause, the expansion period is the overlapping period of the qualified row and the PERIOD constant you specify in the FOR clause. Otherwise, the expanding period is the PERIOD value of the selected row.
  • If the expanding PERIOD expression specifies a column from another table that is not specified in the FROM clause, then the database joins the tables before the expansion.

    You must ensure that the appropriate join condition is specified in such cases. If no other join conditions are specified, then the system performs a Product Join on the referenced tables.

    See Example: Join Before Expansion.

  • When you do not specify an expanded column in the select list, but do specify the DISTINCT operator, the EXPAND operation is nullified.

    See Example: Nullified EXPAND Operation.

  • If the expanding PERIOD is null, then the expanded row has a null expansion.

    See Example: Null Expansion Period Producing a Null Expanded Value.