Nonsequenced Valid-Time Queries - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

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: