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.
- 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.
- 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.