17.05 - Example: Nonsequenced 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 query a transaction-time table such that no special semantics are placed on the transaction-time column, use the NONSEQUENCED TRANSACTIONTIME temporal qualifier in the SELECT statement. For example:

   NONSEQUENCED TRANSACTIONTIME SELECT * FROM Policy_Types;

Policy_Name          Policy_Type  Policy_Duration
-------------------  -----------  ----------------------------------------------------------------------
Premium Automobile   AP          ('2012-06-19 20:04:18.470000-07:00', '9999-12-31 23:59:59.999999+00:00')
Basic Homeowner      HM          ('2012-06-19 20:04:32.410000-07:00', '9999-12-31 23:59:59.999999+00:00')
Business             B           ('2012-06-19 20:04:32.680000-07:00', '9999-12-31 23:59:59.999999+00:00')
Basic Automobile     AU          ('2012-06-19 20:04:32.470000-07:00', '9999-12-31 23:59:59.999999+00:00')
Major Medical        M           ('2012-06-19 20:04:32.550000-07:00', '2012-06-19 20:19:54.100000-07:00')
Premium Homeowner    HP          ('2012-06-19 20:04:32.610000-07:00', '9999-12-31 23:59:59.999999+00:00')

Notice that the Major Medical policy type is a closed row. It shows an end transaction time prior to 9999-12-31. This indicates that the row was logically deleted from the table at the ending transaction time, on 2012-06-19. However, because the row is in a table with a transaction-time dimension, the physical row persists in the table as a closed row.