17.05 - SELECT on Normalized Tables - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
June 2020
Programming Reference

The NORMALIZE keyword can be used optionally in SELECT statements to have the results set normalized. The NORMALIZE option of SELECT can be used on tables that do or do not have NORMALIZE in their table definitions.

The following considerations apply to the use of NORMALIZE with SELECT requests on temporal tables:

  • At least one column in the select list must be a period column or derived period column.
  • The first period column in the select list is the column that is normalized.
  • When a SEQUENCED VALIDTIME SELECT uses NORMALIZE, and no period column is projected in the select list, normalization happens on the system projected “VALIDTIME” column.
  • Normalization cannot be performed using a SELECT statement on a derived period column. As a work around, a true period column can be constructed from the column components of the derived period column.
  • Note that normalization is rarely effective for sequenced select statements on temporal tables that include transaction-time columns, because most of the values in the transaction-time column are likely to be unique, preventing normalization.
  • NORMALIZE can be specified in subqueries.