Example: Nonsequenced Query on a Transaction-Time Table - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

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;

Result:

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.