16.20 - IGNORE NULLS - 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)

If value_expression returns a NULL value where the preceding or following row, as determined by the specified offset_value, is within the scope of the window group, LAG or LEAD ignores the NULL value.

LAG or LEAD then continues searching for the non-NULL value_expression in the preceding or following row, which may be far from the current row but within the scope of the window group. The search terminates at the window boundaries:

  • For LAG, the search terminates at the first row of the window group.
  • For LEAD, the search terminates at the last row of the window group.

At the end of the search, LAG or LEAD returns default_value_expression if no non-NULL value_expression is found.

If the preceding or following row is outside the scope of the window group, LAG or LEAD returns default_value_expression.

If the optional NULL clause is not specified, the default option is RESPECT NULLS.