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

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.