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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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.