17.05 - Syntax - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K
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.
FROM source_spec [,...]
source_spec
{ table_name [ temporal_qualifier ] [ [AS] correlation_name ] |

  ( subquery ) [AS] derived_table_name
    [ ( column_name [,...] ) ] [ temporal_qualifier ] |

  joined_table [ temporal_qualifier ]
    { CROSS JOIN single_table |

      [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ]
        JOIN joined_table [ temporal_qualifier ] ON search_condition
    }
}
temporal_qualifier
{ valid_time_qualifier [ AND transaction_time_qualifier ] |
  transaction_time_qualifier [ AND valid_time_qualifier ] |
  AS OF { date_timestamp_expression | ( date_timestamp_expression ) }
}
valid_time_qualifier
{ VALIDTIME AS OF
  { date_timestamp_expression | ( date_timestamp_expression ) } |
  { CURRENT | NONSEQUENCED } VALIDTIME
}
transaction_time_qualifier
{ TRANSACTIONTIME AS OF
  { date_timestamp_expression | ( date_timestamp_expression ) } |
  { CURRENT | NONSEQUENCED } TRANSACTIONTIME
}
table_name
Specifies the name of a base table, temporal table, derived table, or view.
VALIDTIME AS OF date_timestamp_expression
VALIDTIME AS OF (date_timestamp_expression)
Specifies that the retrieval of rows from table_name only includes rows where the period of validity overlaps the specified AS OF date or timestamp.

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

The data type of date_timestamp_expression must be comparable with the element type of the valid-time column.

AND
Specifies a keyword for specifying both a valid-time AS OF qualifier and a transaction-time AS OF qualifier.
TRANSACTIONTIME AS OF date_timestamp_expression
TRANSACTIONTIME AS OF (date_timestamp_expression)
Specifies that the retrieval of rows from table_name only includes rows where the transaction-time period in the rows overlaps the specified AS OF date or timestamp.

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

The data type of date_timestamp_expression must be comparable with the element type of the transaction-time column.

CURRENT VALIDTIME
Specifies that the retrieval of rows from table_name includes only current rows in the valid-time dimension, which are rows having a period of validity that overlaps TEMPORAL_DATE or TEMPORAL_TIMESTAMP. The valid-time column of table_name is considered to be in the scope of the query, and can therefore be used as any other column in the table, such as in a WHERE or JOIN condition.
CURRENT VALIDTIME in a FROM clause takes precedence over a validtime qualifier at the statement level. For example, consider the following query:
SEQUENCED VALIDTIME SELECT Policy.policy_id 
FROM Policy CURRENT VALIDTIME, Policy_History;
In this case, the SEQUENCED VALIDTIME at the statement level is ignored for Policy, which has a temporal qualifier in the FROM clause. SEQUECED VALIDTIME is applied to Policy_History, however, which does not have a temporal qualifier in the FROM clause.
NONSEQUENCED VALIDTIME
Specifies that table_name is treated as a non-temporal table. Results in a table without a valid-time column.
CURRENT TRANSACTIONTIME
Specifies that the retrieval of rows from table_name includes only rows that are open in the transaction-time dimension.
CURRENT TRANSACTIONTIME in a FROM clause takes precedence over a transactiontime qualifier at the statement level. For example, consider the following query:
NONSEQUENCED TRANSACTIONTIME SELECT Policy_Types.Policy_Name 
FROM Policy_Types CURRENT TRANSACTIONTIME, Policy_History; 
In this case, the NONSEQUENCED TRANSACTIONTIME at the statement level is ignored for Policy_Types, which has a temporal qualifier in the FROM clause. NONSEQUECED TRANSACTIONTIME is applied to Policy_History, however, which does not have a temporal qualifier in the FROM clause.
NONSEQUENCED TRANSACTIONTIME
Specifies that table_name is treated as a non-temporal table. Results in a table without a transaction-time column.
AS OF date_timestamp_expression
AS OF (date_timestamp_expression)
Specifies a date or timestamp value that qualifies the retrieval of rows from table_name in all existing time dimensions.
The data type of date_timestamp_expression must be comparable with the element types of all temporal columns.
[AS] correlation_name
Specifies an alias for the table that is referenced by table_name.
(subquery)
Specifies the subquery that defines the derived table contents.
[AS] derived_table_name
Specifies an assigned name for the temporary derived table.
column_name
Specifies a list of column names or expressions listed in the subquery. Allows referencing subquery columns by name.
joined_table
Specifies either a single table name with optional alias name, or a joined table, indicating nested joins.
AS OF is valid only when joined_table is a single table name with optional alias name.
CROSS JOIN
Specifies a cross join.
A CROSS JOIN is an unconstrained, or extended, Cartesian join.
Cross joins return the concatenation of all rows from the tables specified in its arguments. Two joined tables can be cross joined.
single_table
Specifies the name of a single base or derived table or view on a single table to be cross joined with joined_table.
[INNER] JOIN
Specifies a join in which qualifying rows from one table are combined with qualifying rows from another table according to some join condition.
This is the default join type.
LEFT OUTER JOIN
Specifies a left outer join.
LEFT indicates the table that was listed first in the FROM clause.
In a LEFT OUTER JOIN, matching rows as well as the rows from the left table that are not returned in the result of the inner join of the two tables, are returned in the outer join result and extended with nulls.
RIGHT OUTER JOIN
Specifies a right outer join.
RIGHT indicates the table that was listed second in the FROM clause.
In a RIGHT OUTER JOIN, matching rows as well as the rows from the right table that are not returned in the result of the inner join of the two tables, are returned in the outer join result and extended with nulls.
FULL OUTER JOIN
Specifies a full outer join.
FULL OUTER JOIN returns rows from both tables.
In a FULL OUTER JOIN, matching rows as well as rows from both tables that have not been returned in the result of the inner join, are returned in the outer join result and extended with nulls.
ON search_condition
Specifies one or more conditional expressions that must be satisfied by the result rows.
An ON condition clause is required for each INNER JOIN or OUTER JOIN specified in an outer join expression.