16.20 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

Because the LEAD and LAG functions do not support the ROWS clause in the syntax, the window size is the same as the size of the group of rows defined by the PARTITION BY clause. If the PARTITION BY clause is absent, the entire table becomes a single group, and the size of the group of rows is the same as the total number of rows in the table.

The RESET WHEN clause, which is applicable to all window functions in Teradata Database, is extended to the LEAD and LAG functions.

The RESET WHEN clause is a Teradata Extension to ANSI. The LEAD and LAG functions support performance-driven rewrites, and support both Teradata syntax and ANSI syntax to simplify data migration from other databases.

In ANSI Transaction mode, the value_expression data type must match the default value expression data type, or else an error occurs.