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 window partitions.
The following rules apply for RESET WHEN conditions.
- 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 specified as a standalone condition in the predicate. For more information, see Rules for Using a DEFAULT Function in a RESET WHEN Condition.
- 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 if that expression returns a result that has a predefined data type.