EXPAND ON Clause Syntax | SQL SELECT Statements | Teradata Vantage - EXPAND ON Clause Syntax - Analytics Database - Teradata Vantage

EXPAND ON expand_expression [AS] expand_column_alias
  [ BY { interval_literal |
         ANCHOR [PERIOD] anchor_name [ AT time_literal ]
       }
  ] [ FOR period_expression ]

Syntax Elements

expand_expression

Time series expansion information in a query expression.

The EXPAND ON clause generates a regular time series as a sequence of values at each of the granules or at each point of a predefined interval in the specified period from an input row.

The expand_expression variable specifies a PERIOD column name or PERIOD expression on which the selected rows are to be expanded. This is referred to as the input row for the EXPAND ON operation.

If the expansion period is null, then the expanded row has a null expanded value.

The specified column or expression must have a PERIOD data type or can be a derived period column.

expand_column_alias

Aliased name of the PERIOD expression to be expanded.

The aliased column or expression must have a PERIOD data type.

You can reference expand_column_alias in the select list, including inside an expression that does not reference any other columns and refers to the expanded value for an expanded row.

You cannot specify expand_column_alias in other clauses within the same query block except for an ORDER BY clause.

You cannot specify expand_column_alias in any clauses in a subquery or correlated subquery, nor can you specify it in any aggregate or statistical function in the select list.

interval_literal

Interval expression by which expand_column_name is to be expanded.

This value specifies the granularity of the value-equivalent rows produced by the EXPAND ON clause. For a complete list of the expand interval literals, see Expand Interval Literal Expressions.

If you do not specify the BY interval_literal option, the expansion interval defaults to the granularity of the element type of the PERIOD value for the column.

The expansion commences from the beginning value of the expansion period and terminates at the ending value of the expansion period, incrementing by interval_literal for each expanded row.

In this process, the last expanded row may not cover the expansion interval duration, producing only a partial period. By definition, a partial period is a period with a duration that is less than the expanded interval.

anchor_name

Expansion is an anchor PERIOD expansion.

Specify PERIOD only for anchor PERIOD expansions.

• If you specify PERIOD for noninterval data, then the expansion is an Anchor PERIOD expansion.
• If you do not specify PERIOD for noninterval data, then the expansion is an Anchor Point expansion.

The variable anchor_name specifies an anchor name literal. For a complete list of the anchor name literals, see Anchor Name Literal Expressions.

time_literal

Optional Time literal. For a complete list of Time literals, see SQL Data Types and Literals.

If you do not specify a time literal value, the value defaults to ‘00:00:00.000000 00:00’ for an ANCHOR_MILLISECOND, ANCHOR_SECOND, ANCHOR_MINUTE, ANCHOR_HOUR, WEEK_BEGIN, MONTH_BEGIN, QUARTER_BEGIN, or YEAR_BEGIN anchor and to ‘23:59:59.999999 00:00’ for any other anchors.

period_expression

Limit the number of rows to be expanded, where period_expression represents the period of interest. For a comprehensive list of valid PERIOD expressions, see Teradata Vantage™ - Data Types and Literals, B035-1143.

The expansion period is the overlapping period of the qualified row and the PERIOD constant you specify as period_expression.

Otherwise, the expanding period is the PERIOD value of the selected row.

The data type of period_expression must be comparable with the PERIOD data type of the expanded column.

If the specified PERIOD expression is either null or does not overlap with the row, then the database does not expand the row because it does not qualify for expansion.