17.05 - Example: As Of Query on a Transaction-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 get a snapshot of a transaction-time table where the transaction-time period in the result rows overlap a specific time, use the TRANSACTIONTIME AS OF temporal qualifier in the SELECT statement. For example:

   TRANSACTIONTIME AS OF DATE '2012-06-20' SELECT * FROM Policy_Types;

Policy_Name           Policy_Type
--------------------  -----------
Premium Automobile    AP
Basic Homeowner       HM
Business              B
Basic Automobile      AU
Premium Homeowner     HP

Notice that the Major Medical policy type that was logically deleted from the table on 2012-06-19 does not appear in the result set, though it did appear in Example 5 when the NONSEQUENCED TRANSACTIONTIME qualifier to SELECT was used.