Syntax - SELECT … INTO - Advanced SQL Engine - Teradata Database

[ valid_time_qualifier [ AND transaction_time_qualifier ] |
  transaction_time_qualifier [ AND valid_time_qualifier ] |
  AS OF date_timestamp_expression
]
select_into_statement

valid_time_qualifier

{ CURRENT VALIDTIME |
  VALIDTIME AS OF date_timestamp_expression |
  { [ SEQUENCED | NONSEQUENCED ] VALIDTIME } [ period_expression ]
}

transaction_time_qualifier

{ { CURRENT | NONSEQUENCED } TRANSACTIONTIME |
  TRANSACTIONTIME AS OF date_timestamp_expression
}

CURRENT VALIDTIME

Specifies that the query is current in the valid-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

VALIDTIME AS OF date_timestamp_expression

Specifies that the query is AS OF in the valid-time dimension.

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

The expression can be any DATE or TIMESTAMP[(n)] [WITH TIME ZONE] expression, including parameterized values and built-in functions such as CURRENT_DATE or TEMPORAL_DATE, that does not reference any columns. One exception to this rule is that the expression can be a self-contained noncorrelated scalar subquery. A noncorrelated scalar subquery is always assumed to be nonsequenced in the time dimensions regardless of the temporal query qualifier.

If the date_timestamp_expression specifies TEMPORAL_DATE or TEMPORAL_TIMESTAMP, the values of these built-in functions evaluate to the time of the transaction.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

VALIDTIME and SEQUENCED VALIDTIME

Specifies that the query is sequenced in the valid-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

A sequenced valid-time query results in a valid-time table. The valid-time period for each row in the result set is the overlap of original row valid-time with the period_expression specified in the query. The valid-time column in the result set a new column named VALIDTIME, which is automatically appended to the results.

If period_expression is omitted, the period of applicability for a sequenced query defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' where the data type is PERIOD(DATE) or PERIOD'(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)’ where the data type is PERIOD(TIMESTAMP). In these cases, the valid-time periods of the rows in the result set matches the valid-time periods of the original rows in the queried temporal table.

NONSEQUENCED VALIDTIME

Specifies that the query is nonsequenced in the valid-time dimension. The system does not associate any special meaning to the valid-time column. The query can use the valid-time column like any other column.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

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.

For a nonsequenced query, the period of applicability can either be a period value expression that does not reference any column names or an alias name to a period column or period expression.

When an SELECT … INTO statement specifies period_expression, the number of elements in the INTO target list (that specifies the variables into which the selected values must be saved) must be one more than the projected elements in the select list. This additional target element saves the resulting period of validity of the output row.

AND

Specifies a keyword for specifying both a valid-time qualifier and a transaction-time qualifier.

CURRENT TRANSACTIONTIME

Specifies that the query is current in the transaction-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports transaction time.

TRANSACTIONTIME AS OF date_timestamp_expression

Specifies that the query is AS OF in the transaction-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports transaction time.

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

Specifies that the query is nonsequenced in the transaction-time dimension.

In a nonsequenced query, the system does not associate any special meaning to the transaction-time column. The query can use the transaction-time column like any other column.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports transaction time.

AS OF date_timestamp_expression

Specifies the query is an AS OF query in both the valid-time and transaction-time dimensions.

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

All tables with valid time referenced in the query use the specified qualifier to qualify in the valid-time dimension. Similarly, all tables with transaction time referenced in the query use the qualifier to qualify in the transaction-time dimension.

At least one table referenced in the query must be a temporal table. If only valid-time tables and nontemporal tables are referenced, the AS OF qualifier is equivalent to specifying VALIDTIME AS OF date_timestamp_expression. If only transaction-time tables and nontemporal tables are referenced, the AS OF qualifier is equivalent to specifying TRANSACTIONTIME AS OF date_timestamp_expression. If both valid-time and transaction-time tables are referenced, the AS OF qualifier is equivalent to specifying VALIDTIME AS OF date_timestamp_expression AND TRANSACTIONTIME AS OF date_timestamp_expression.

select_statement

Specifies conventional SELECT statement syntax, with temporal table support enhancements to the FROM clause.

For details on FROM, see FROM Clause (Temporal Form).

select_into_statement

Specifies conventional SELECT … INTO statement syntax, with temporal table support enhancements to the FROM clause.

For details on FROM, see FROM Clause (Temporal Form).