RESET WHEN Condition Rules - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.