An Expand On element may only be used underneath an Expand On clause, which can be used to generate a time-series expansion of a Period type column or expression. Dragging an Expand On element underneath the Expand On Clause gives the following result.
The first sub-node underneath the Expand On element is required and must resolve to a Period data type. The second and third sub-nodes underneath the Expand On element are optional and their use depends on the properties selected on the Properties panel for the Expand On element, described below. The approach taken here is that sub-nodes are used for parameters that require a SQL expression, and other parameters can or must be selected on the Properties panel.
To display the Properties panel for the Expand On Clause, highlight the Expand On element and click Properties to the right of the SQL element tree.
By default, the Expand On Type is set to By Interval, in which case the middle sub-panel is labeled BY Expand Interval and displays in read-only fashion the interval literal entered as the second of three expressions underneath the Expand On SQL element. If, however, By Anvchore Period or By Anchor Point is selected as the Expand On Type, the middle sub-panel is labeled By Anchor and the following dialog appears.
- Expand On Period (sub-node) — This is the required sub-node of the Expand On node containing the Period type column or expression that is the basis of the time-series expansion. Note that this element is displayed in a read-only fashion as (period expression) on the Expand On sub-panel.
- Expand Column Alias — This required field provides an alias for the expanded value of the period of expansion. This alias can be referenced in the Variables defined in this Variable Creation analysis, using the Expand Column Reference in the Other category of SQL elements.
-
Expand On Type
The possible types include:
- By Interval — Expansion is performed based on a user-defined date/time interval.
- By Anchor Period — Expansion is performed based on user-specified anchored time durations on a time line.
-
By Anchor Point — Expansion is performed based on user-specified anchored points on a time line.
As mentioned above, the selection made here affects the options displayed on the Properties dialog, and is the key to the type of expansion performed. For more information, refer to the Teradata user documentation mentioned earlier.
- Expand On Interval (sub-node) — When Expand On Type is set to By Interval, this element must contain an Interval literal that defines how the Expand On Period is segmented or expanded. Note that this element is displayed in a read-only fashion as (interval literal) on the BY Expand Interval sub-panel.
-
Anchor Name — This field allows the selection of a keyword describing the expansion interval. The values available in Teradata release 13.10 are shown with release blank in the table below. Beginning in Teradata release 14.00, the values representing the beginning or ending value of a calendar period are affected by the system business calendar set in the current session.
Value | Available Version |
---|---|
Anchor_Millesecond | |
Anchor_Second | TD 14.0 |
Anchor_Minute | TD 14.00 |
Anchor_Hour | TD 14.00 |
Day | TD 14.00 |
Monday | |
Tuesday | |
Wednesday | |
Thursday | |
Friday | |
Saturday | |
Sunday | |
Week_Begin | TD 14.00 |
Week_End | TD 14.00 |
Month_Begin | |
Month_End | |
Quarter_Begin | TD 14.00 |
Quarter_End | TD 14.00 |
Year_Begin | TD 14.00 |
Year_End | TD 14.00 |
- At Time Literal — If the element type of the Period expansion expression is Timestamp, the At Time Literal is used to determine the Time portion of the Timestamp value of the anchor during expansion. If not specified here, a default of '00:00:00.000000+00:00' is used for anchored time values and begin-type anchors, and '23:59:59.999999+00:00' is used otherwise.
- For Period (sub-node) — This optional sub-node represents the period of interest for expansion, effectively limiting the number of rows in the expansion. The overlapping period of the qualified row and the period constant specified here is the effective expansion period for each row. Note that this element is displayed in a read-only fashion as (period expression) on the FOR sub-panel.