- Temporal qualifiers can only be used on tables with corresponding temporal dimensions. For example you can only use a VALIDTIME qualifier on a valid-time or bitemporal table.
- A temporal qualifier in a FROM clause takes precedence over a qualifier for the same temporal dimension 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. SEQUENCED VALIDTIME is applied to Policy_History, however, which does not have a temporal qualifier in the FROM clause. - The valid-time or transaction-time column of table_name that corresponds to the FROM clause temporal qualifier is considered to be in the scope of the query, and can therefore be referenced anywhere in the query, such as in a WHERE or JOIN condition.
- You can apply the AS OF qualifier to valid-time and transaction-time dimensions independently.
- If date_timestamp_expression of an AS OF qualifier references a table column, Teradata Database applies the AS OF qualifier to the temporal table for each value in the referenced column, and returns the combination of all results.
SELECT Policy.customer_id,Incident.Id FROM Policy AS OF Incident.Injury_date WHERE Policy.customer_id = Incident.customer_id ; - If date_timestamp_expression references a table column, and that table is involved in a join using an explicit JOIN clause, the conditions resulting from the AS OF qualification are added to the WHERE clause, not the ON clause. For example, this SELECT statement:
SELECT Policy.customer_id,Incident.Id FROM Policy AS OF Incident.Injury_date LEFT OUTER JOIN Incident ON Policy.customer_id = Incident.customer_id;would be equivalent to this SELECT statement:SELECT Policy.customer_id,Incident.Id FROM (NONSEQUENCED VALIDTIME SELECT * FROM Policy )Policy LEFT OUTER JOIN Incident ON Policy.customer_id = Incident.customer_id WHERE Policy.Policy_Term CONTAINS Incident.Injury_date;
- The table associated with a temporal qualifier in the FROM clause of an UPDATE or DELETE statement cannot be the table that the DELETE or UPDATE statement modifies.