17.05 - Temporal Queries in Set Operations - 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

When temporal tables are referenced in queries involving set operations (UNION, INTERSECT, MINUS, and EXCEPT) all queries inherit the temporal qualifier from the topmost query.

If temporal qualifications are required at the level of individual queries, add them to the FROM clause or place the query with the required qualification in a derived table.

Example: Proper form of temporal queries using set operations

The following query would yield an error:

VALIDTIME AS OF DATE '2009-05-06'
SELECT *
FROM v1

MINUS

VALIDTIME AS OF DATE '2009-05-05'
SELECT *
FROM v1

ORDER BY 1;

The following query would run properly to yield the desired results:

SELECT *
FROM v1 VALIDTIME AS OF DATE '2009-05-06'

MINUS

SELECT *
FROM v1 VALIDTIME AS OF DATE '2009-05-05'

ORDER BY 1;