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

• 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.”

• 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 “CREATE JOIN INDEX” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
• The query expression is a SELECT AND CONSUME statement. See SELECT AND CONSUME.

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.

• 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.

• 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 “CREATE JOIN INDEX” in 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.

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

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

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 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.

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.

See Example: Nullified EXPAND Operation.

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