17.10 - Example: Sequenced Valid-Time Query - 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)
SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
SELECT *
FROM Policy;

Policy_ID Customer_ID Policy_Type Policy_Details      VALIDTIME
--------- ----------- ----------- ------------------- ------------------------
   541077   766492008 AU          STD-CH-344-YXY-00   ('09/12/21', '09/12/31')
   541008   246824626 AU          STD-CH-345-NXY-00   ('09/10/01', '09/12/31')
   541145   616035020 AU          STD-CH-348-YXN-01   ('09/12/03', '09/12/31')

Although VALIDTIME is the valid-time column of the result set, Validity is the valid-time column of the originally queried Policy table. To show the Validity column in the results requires a subquery, because the valid-time column name cannot appear anywhere in a query that includes a PA. Use a sequenced validtime subquery that does not specify a PA. Because the Validity column is not the valid-time column of the derived table, it can be retrieved using a sequenced outer query that includes a PA: