17.05 - Rules and Restrictions for Anchor Point Expansion of 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 following cases are possible with Anchor Point Expansion when the expansion interval is longer than the granularity of the element type of the expand expression:
  • There is no single anchor point in the expansion period.

    For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2007-08-14', DATE '2007-08-17'), then the expansion period starts on Tuesday and ends on Friday in the same week.

    In this case, the input row does not produce any expanded rows in the expanded result.

  • The beginning bound of the expansion period is not aligned to an anchor point and its duration is longer than the expansion interval.

    In this case, the expanded row set does not have a row corresponding to the beginning bound of the expansion period.

    For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2007-08-15', DATE '2007-08-25'), the expanded result contains only one point, which is DATE '2007-08-20'.

  • The beginning bound of the expansion period is aligned with an anchor point.

    In this case, the expanded rows contain a row with the beginning bound of the expansion period.

    For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2007-08-20', DATE '2007-08-25'), then the last expanded row contains DATE '2007-08-20'.

The expanded rows correspond to all anchor points (the BEGIN point of an anchor period) that are within the expansion period. Each row expands to as many anchor points that exist in the expansion period.