When the AS OF clause is specified as a temporal qualifier either explicitly in the statement or implicitly as a session temporal qualifier, its usage covers the entire query. (The AS OF clause can also be specified in the FROM clause, but such usage covers only the corresponding table in the FROM clause. For details, see FROM Clause (Temporal Form).)
date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.
The following rules apply to As Of valid-time queries on valid-time tables:
- As Of valid-time queries on valid-time tables produce snapshot tables as results.
- When the AS OF temporal qualifier is specified in the valid-time dimension, it applies to all valid-time tables in the query. The valid-time columns of the valid-time tables are in the scope of the query and they can be used anywhere in the query block, including the WHERE condition and JOIN condition.
- The behavior of a query with an AS OF temporal qualifier is as if a current query was issued at the specified AS OF time. However, a current query reads only valid rows and an As Of query can read rows that are no longer valid.
- Specifying the AS OF qualifier in the valid-time dimension serves as an additional qualification criteria that only rows with a period of validity that overlaps the specified time are eligible to participate in the query. Thereafter, the query treats all the underlying tables as non-valid-time tables. Operations such as joins, aggregations, and set operations are not impacted by this qualifier.
- The data type of date_time_expression must be comparable with the element type of the valid-time columns. The following rules apply.
| Data Type of date_time_expression | Element Type of Temporal Column | Details |
|---|---|---|
| DATE | DATE | The data types are comparable. |
| DATE | TIMESTAMP[(n)] [WITH TIME ZONE] | The DATE value is cast to TIMESTAMP(n) and used for qualification. The time portion of the converted timestamp value is 00:00:00 (hh:mi:ss) in the session time zone. The row is qualified based on the UTC timestamp values. |
| TIMESTAMP[(n)] [WITH TIME ZONE] | DATE | The temporal column value is cast to TIMESTAMP and used for qualification. The time portion of the converted timestamp value is 00:00:00 (hh:mi:ss) in the session time zone. The row is qualified based on the UTC timestamp values. |
| TIMESTAMP[(n)] [WITH TIME ZONE] | TIMESTAMP[(m)] [WITH TIME ZONE] | The timestamp value with coarser precision is converted to the finer precision and then the rows are qualified. |
| Any other data type | DATE | Teradata Database reports an error. |
| TIMESTAMP[(n)] [WITH TIME ZONE] |
If the query involves a bitemporal or transaction-time table, refer to the following topics for additional information that applies to the transaction-time dimension: