Syntax - Advanced SQL Engine - Teradata Database

Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
cjo1556732840654.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1182
lifecycle
previous
Product Category
Teradata Vantage™
Teradata Vantage™ - Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this document. For additional syntax, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 , Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 , and Teradata Vantage™ - SQL Data Control Language, B035-1149.
[ valid_time_qualifier [ AND transaction_time_qualifier ] |
  transaction_time_qualifier [ AND valid_time_qualifier ] |
  AS OF date_timestamp_expression
]
ABORT [ 'message' ] [ FROM option ] [ WHERE abort_condition ]
To ensure application portability to ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.
valid_time_qualifier
{ { CURRENT | NONSEQUENCED } VALIDTIME |
  VALIDTIME AS OF date_timestamp_expression |
  [ SEQUENCED ] VALIDTIME [ period_expression ]
}
transaction_time_qualifier
{ { CURRENT | NONSEQUENCED } TRANSACTIONTIME |
  TRANSACTIONTIME AS OF date_timestamp_expression
}
CURRENT VALIDTIME
Specifies that only rows that are currently valid participate in the evaluation of the abort condition.
At least one table referenced in the statement must have valid time.
VALIDTIME AS OF date_timestamp_expression
Specifies a given time that must overlap the valid time of a row for that row to participate in the evaluation of the abort condition.
At least one table referenced in the statement must have valid time.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

SEQUENCED VALIDTIME
Specifies a period of applicability that must overlap the period of validity of a row for that row to participate in the evaluation of the abort condition.
For more information see Sequenced Valid-Time Queries.
period_expression

Specifies the period of applicability for the DML statement.

The period of applicability must be a period constant expression that does not reference any columns, but can reference parameterized values and the TEMPORAL_DATE or TEMPORAL_TIMESTAMP built-in functions.

The period of applicability can also be a self-contained noncorrelated scalar subquery that is always nonsequenced in the time dimensions regardless of the temporal qualifier for the DML statement.

If a period_expression is specified, the valid-time column cannot be specified or referenced anywhere in the query. If the valid-time column is a derived period column, the component columns cannot be specified or referenced anywhere in the query.

If period_expression is omitted, the period of applicability defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP( n ) WITH TIME ZONE) valid-time column, where precision n and WITH TIME ZONE are optional.

NONSEQUENCED VALIDTIME
Specifies that rows that participate in the evaluation of the abort condition are not further evaluated for qualification in the valid-time dimension.
At least one table referenced in the statement must have valid time.
AND
Specifies a keyword for specifying both a valid-time qualifier and a transaction-time qualifier.
CURRENT TRANSACTIONTIME
Specifies that only rows that are open participate in the evaluation of the abort condition.
At least one table referenced in the statement must have transaction time.
TRANSACTIONTIME AS OF date_timestamp_expression
Specifies a given time that must overlap the transaction time of a row for that row to participate in the evaluation of the abort condition.
At least one table referenced in the statement must have transaction time.
NONSEQUENCED TRANSACTIONTIME
Specifies that rows that participate in the evaluation of the abort condition are not further evaluated for qualification in the transaction-time dimension.
At least one table referenced in the statement must have transaction time.
AS OF date_timestamp_expression
Specifies that only rows that overlap date_timestamp_expression in the valid-time and transaction-time dimension participate in the evaluation of the abort condition.
'message'
Specifies the text of the message to be returned when the transaction is terminated.
FROM option
Specifies the temporal tables that are further qualified in the WHERE clause.
WHERE abort_condition
Specifies an expression where the result must evaluate to TRUE for Teradata Database to roll back the transaction.