17.10 - Example: As Of Query on a Transaction-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 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.