17.10 - Use of QUALIFY Clause - Advanced SQL Engine - Teradata Database

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

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

Rows can be eliminated by applying conditions on the new column value. The QUALIFY clause is analogous to the HAVING clause of aggregate functions. The QUALIFY clause eliminates rows based on the function value, returning a new value for each of the participating rows. For example:

   SELECT StoreID, SUM(profit) OVER (PARTITION BY StoreID)
   FROM facts
   QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;

An SQL query that contains both ordered analytical functions and aggregate functions can have both a QUALIFY clause and a HAVING clause, as in the following example:

   SELECT StoreID, SUM(sale), 
   SUM(profit) OVER (PARTITION BY StoreID)
   FROM facts
   GROUP BY StoreID, sale, profit
   HAVING SUM(sale) > 15
   QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;