IGNORE NULLS

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.