16.20 - RESET WHEN Condition Rules - Teradata Vantage NewSQL Engine

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

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

The condition in the RESET WHEN clause is equivalent in scope to the condition in a QUALIFY clause with the additional constraint that nested ordered analytical functions cannot specify conditional partitioning.

The condition is applied to the rows in all designated window partitions to create sub-partitions within the particular window partitions.

The following rules apply for RESET WHEN conditions.

A RESET WHEN condition can contain the following:
  • Ordered analytical functions that do not include the RESET WHEN clause
  • Scalar subqueries
  • Aggregate operators
  • DEFAULT functions

    However, DEFAULT without an explicit column specification is valid only if it is specified as a standalone condition in the predicate. For more information, see Rules For Using a DEFAULT Function As Part of a RESET WHEN Condition.

A RESET WHEN condition cannot contain the following:
  • Ordered analytical functions that include the RESET WHEN clause
  • The SELECT statement
  • LOB columns
  • UDT expressions, including UDFs that return a UDT value

    However, a RESET WHEN condition can include an expression that contains UDTs as long as that expression returns a result that has a predefined data type.