15.00 - SET SESSION (Session Temporal Qualifiers) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

SET SESSION (Session Temporal Qualifiers)

Purpose  

Sets the session temporal qualifier in the valid-time dimension, transaction-time dimension, or valid-time and transaction-time dimensions.

Note: Best practice for maximal application portability is to explicitly qualify all queries and DML that operates on temporal tables, rather than relying on session temporal default qualifiers.

Syntax  

Note: 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 manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

 

Syntax Element …

Specifies …

CURRENT VALIDTIME

that the session valid-time qualifier is current.

VALIDTIME AS OF date_timestamp_expression

that when a SELECT statement refers to a temporal table but omits a valid-time qualifier, the default behavior extracts a snapshot of the table in the valid-time dimension AS OF the date or timestamp value specified by date_timestamp_expression.

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

VALIDTIME
SEQUENCED VALIDTIME

that the session valid-time qualifier is sequenced.

NONSEQUENCED VALIDTIME

that the session valid-time qualifier is nonsequenced.

period_expression

the period of applicability for the sequenced or nonsequenced session valid-time qualifier. The period value must be a constant expression or built-in function and cannot reference any columns.

CURRENT TRANSACTIONTIME

that the session transaction-time qualifier is current.

TRANSACTIONTIME AS OF date_timestamp_expression

that when a SELECT statement refers to a temporal table but omits a transaction-time qualifier, the default behavior extracts a snapshot of the table in the transaction-time dimension AS OF the date or timestamp value specified by date_timestamp_expression.

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

NONSEQUENCED TRANSACTIONTIME

that the session transaction-time qualifier is nonsequenced.

AS OF date_timestamp_expression

that when a SELECT statement refers to a temporal table but omits the valid-time qualifier, transaction-time qualifier, or both, the default behavior extracts a snapshot of the table in the valid-time dimension, transaction-time dimension, or both dimensions AS OF the value specified by date_timestamp_expression.

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

ANSIQUALIFIER

This is the default for users of ANSI/ISO-compatible temporal tables and syntax.

The session temporal qualifier must be ANSIQUALIFIER in order to create ANSI-compatible temporal tables and to get ANSI-compatible database behavior for temporal queries and DML.

The session qualifier must also be ANSIQUALIFIER in order to convert existing temporal tables described in this manual to ANSI-compatible temporal tables described in ANSI Temporal Table Support. For more information on converting transaction-time tables to system-time tables, see Appendix E: “ANSI Temporal Tables.”

Usage Notes  

When a SELECT or DML statement refers to a temporal table but does not explicitly specify a valid-time qualifier, the system uses the session valid-time qualifier. Similarly, when a SELECT statement refers to a temporal table but does not explicitly specify a transaction-time qualifier, the system uses the session transaction-time qualifier.

The current session temporal qualifier is reported by the Temporal Qualifier field returned by the HELP SESSION statement.

The default temporal session qualifier depends on whether you use the original Teradata Database implementation of temporal tables and syntax, as described in this manual, or the ANSI/ISO-compliant implementation of temporal tables, as described in the ANSI Temporal Tables and Syntax manual. This default is determined by Temporal Behavior setting in DBS Control, and is set by Teradata personnel in accordance with your use of temporal tables and syntax. For more information on DBS Control, see Utilities: Volume 1 (A-K).

Related Information

 

For more information on...

See...

SET SESSION (regular form)

SQL Data Definition Language

session temporal qualifier

“Session Temporal Qualifiers” on page 27

ANSI/ISO-compatible temporal tables and syntax

ANSI Temporal Table Support