FROM Clause Syntax Elements (ANSI Valid-Time Table Form) - Analytics Database - Teradata Vantage

ANSI Temporal Table Support
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
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantage™
valid_time_table
The valid-time table being queried.
AS OF
Qualifies rows in valid-time tables with valid-time periods that include the specified point in time. This means the information in the row is, was, or will be valid at the specified point in time.
Although the returned rows are valid at the time specified by the query, they may not be valid at the time the query is submitted.
point_in_time
point_in_time_1
point_in_time_2
Identifies the point in valid time or delimits the period in valid time for which rows will be returned.
A date or timestamp expression that can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a DATE or TIMESTAMP[(n)] [WITH TIME ZONE] value.
The expression can be any expression, including parameterized values and built-in functions such as CURRENT_DATE, CURRENT_TIMESTAMP, TEMPORAL_DATE, or TEMPORAL TIMESTAMP. The expression cannot reference any columns, but it can be a self-contained noncorrelated scalar subquery.
BETWEEN ... AND
Qualifies all rows with valid-time periods that overlap or immediately succeed the period defined by point_in_time_1 and point_in_time_2.
This is not the commonly understood meaning of “between” because BETWEEN will qualify rows with valid-time periods that begin before by point_in_time_1, and rows that start immediately after point_in_time_2 and extend beyond that. For a qualifier that reflects the commonly understood meaning of BETWEEN, use the CONTAINED IN qualifier.
FROM TO
Qualifies all rows with valid-time periods that overlap the period defined by point_in_time_1 and point_in_time_2.
CONTAINED IN
Qualifies all rows with valid-time periods that are between point_in_time_1 and point_in_time_2. The valid-time period starts at or after point_in_time_1, and ends before or at point_in_time_2.
CONTAINED IN is a Teradata extension to ANSI.