Usage Notes - 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™

A temporal qualifier can be specified for the outermost SELECT statement, in a derived table, view, join index and other DDL statements.

A temporal qualifier cannot be specified in a subquery. A subquery inherits the temporal qualifier of its parent query, with one exception. When the qualifier is NONSEQUENCED VALIDTIME period_expression, the subquery does not inherit the specified period of applicability.

The absence of a valid-time qualifier in the statement makes the query current in the valid-time dimension if no session valid-time qualifier is available. The absence of a transaction-time qualifier in the statement makes the query current in the transaction-time dimension if no session transaction-time qualifier is available.

The following table provides the meanings for the various combinations of qualifiers for the temporal form of a query.

Temporal SELECT and Options Meaning
  • SELECT
  • CURRENT VALIDTIME SELECT
  • CURRENT TRANSACTIONTIME SELECT
  • CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
Query is current in valid time and current in transaction time.
  • VALIDTIME SELECT
  • SEQUENCED VALIDTIME SELECT
  • VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
Query is sequenced in valid time and current in transaction time.
  • VALIDTIME period_expression SELECT
  • SEQUENCED VALIDTIME period_expression SELECT
  • VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
Query is sequenced in valid time and current in transaction time. The rows of interest in the valid-time dimension are all those rows whose valid time overlaps the specified period_expression.
  • NONSEQUENCED VALIDTIME SELECT
  • NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
Query is nonsequenced in valid time and current in transaction time.
  • NONSEQUENCED VALIDTIME period_expression SELECT
  • NONSEQUENCED VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
Query is nonsequenced in valid time and current in transaction time. The result of the query is a valid-time result with the valid-time value set as the specified period_expression.
  • VALIDTIME AS OF date_timestamp_expression SELECT
  • VALIDTIME AS OF date_timestamp_expression AND CURRENT TRANSACTIONTIME SELECT
Query is AS OF in valid time and current in transaction time.
  • NONSEQUENCED TRANSACTIONTIME SELECT
  • CURRENT VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and current in valid time.
  • VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and sequenced in valid time.
  • VALIDTIME period_expression AND NONSEQUENCED TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME period_expression AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and sequenced in valid time. The rows of interest in the tables with valid time support are those rows whose valid time overlaps the specified period_expression. 
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
 Query is nonsequenced in transaction time and nonsequenced in valid time. 
NONSEQUENCED VALIDTIME  period_expression  AND NONSEQUENCED TRANSACTIONTIME SELECT
 Query is nonsequenced in transaction time and nonsequenced in valid time. The result of the query is a valid-time result with the valid-time value set to the specified period_expression. 
VALIDTIME AS OF  date_timestamp_expression  AND NONSEQUENCED TRANSACTIONTIME SELECT
 Query is nonsequenced in transaction time and AS OF in valid time.
  • TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • CURRENT VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and current in valid time.
  • VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • SEQUENCED VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and sequenced in valid time.
  • VALIDTIME period_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • SEQUENCED VALIDTIME period_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and sequenced in valid time. The rows of interest in the valid-time tables are those rows whose valid time overlaps the specified period_expression. 
NONSEQUENCED VALIDTIME AND TRANSACTIONTIME AS OF  date_timestamp_expression  SELECT
 Query is AS OF in transaction time and nonsequenced in valid time. 
NONSEQUENCED VALIDTIME  period_expression  AND TRANSACTIONTIME AS OF  date_timestamp_expression  SELECT
 Query is AS OF in transaction time and nonsequenced in valid time. The result of the query is a valid-time result with the valid-time value in the rows set to period_expression value.
  • VALIDTIME AS OF date_timestamp_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and AS OF in valid time.