17.10 - Nonsequenced Valid-Time Queries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

A nonsequenced query operates on all the valid-time states of the underlying table (history, current, future) simultaneously. Such a query is very powerful because it can link across states.

Use a nonsequenced valid-time query to ignore the time-varying nature of a table or when the computation of a single state of the result table utilizes the information from a state at a different time.

A nonsequenced query treats the valid-time column as if it is simply a regular column that contains a period value. The query can specify this column anywhere in the query, just as any column.

A nonsequenced query can select rows with NULL in the valid-time column. None of the other temporal SELECT qualifiers will select such rows.

If the NONSEQUENCED VALIDTIME qualifier does not specify period_expression, the nonsequenced query on a valid-time table results in a non-valid-time table.

If period_expression is specified, the result of the query is a valid-time table. However, the valid-time column of the results table is not the same column as the valid-time column in the queried table. The results table includes an additional column named VALIDTIME, that serves as the valid-time column. The value of VALIDTIME in each row is the period of applicability that was specified in the query. This form of a nonsequenced valid-time query can be used to convert a nontemporal table to a table with valid time. Such a query is not permitted in a request that specifies a SET operator (UNION, INTERSECT and MINUS).

If the projection list is *, the valid-time column is projected with all other non-valid-time columns.

A reference to VALIDTIME in the same select block in other clauses such as WHERE condition follows the existing resolution rules, and cannot reference the system-projected column. For example, if none of the referenced tables has a column named VALIDTIME an error results. No column in the projection list can use VALIDTIME as an alias.

If an alias name is specified as the period of applicability, the alias name must be in the scope of the select (projection) list and unambiguously referenced in the projection list.

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: