17.10 - Example: Sequenced Query on a Valid-Time Table - 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)

To query a valid-time table for the rows that are valid at a specific time period, use the SEQUENCED VALIDTIME temporal qualifier in the SELECT statement. For example:

   SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
   SELECT Policy_ID, Customer_ID
   FROM Policy
   WHERE Policy_Type = 'AU';

The result set is a temporal table that includes rows that are valid for a period of applicability specified by PERIOD '(2009-01-01, 2009-12-31)':

   Policy_ID  Customer_ID  VALIDTIME
   ---------  -----------  ------------------------
      541077    766492008  ('09/12/21', '09/12/31')
      541145    616035020  ('09/12/03', '09/12/31')
      541008    246824626  ('09/10/01', '09/12/31')
The valid-time column for the result set, VALIDTIME, which is automatically appended to the results of a sequenced valid-time query, is different from the valid-time column (Policy_Term) of the temporal table that was queried. The valid time for the results of the query is the intersection of the PA of the query and the original valid-time periods of the rows.