Rules and Restrictions for Anchor Point Expansion of the EXPAND ON Clause - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language
Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™
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.