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

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;