QUALIFY Clause
Purpose
Filters results of a previously computed ordered analytical function according to user‑specified search conditions.
Syntax
where:
|
Syntax element … |
Specifies … |
|
QUALIFY |
a conditional clause in the SELECT statement. |
|
search_condition |
one or more conditional expressions that must be satisfied by the result rows. You can use aggregate operators and DEFAULT functions within a QUALIFY search condition. The QUALIFY condition cannot reference LOB columns. If you specify the value for a row‑level security constraint in a search condition, that value must be expressed in its encoded form. |
ANSI Compliance
The QUALIFY clause is a Teradata extension to the ANSI standard.
Rules and Restrictions for the QUALIFY Clause
The rules and restrictions are:
The following query fails because of the OR condition connected with the subquery.
SELECT RANK (column_1)
FROM table_1
QUALIFY column_1 IN (SELECT table_2.column_1
FROM table_2)
OR table_1.column.1 = 10000;
A condition other than IS NULL or IS NOT NULL with a DEFAULT function compared with a null evaluates to unknown.
|
IF a DEFAULT function is used with … |
THEN the comparison is … |
|
IS NULL |
|
|
IS NOT NULL |
|
See SQL Functions, Operators, Expressions, and Predicates for more information about the DEFAULT function.
Evaluation Order of WHERE, GROUP BY, and QUALIFY Clauses
When the WHERE, GROUP BY, and QUALIFY clauses are used together in a SELECT statement, the order of evaluation is as follows:
1 WHERE clause
2 GROUP BY clause
3 QUALIFY clause
The detailed steps are as follows:
1 Teradata Database evaluates the WHERE clause conditions on the FROM clause tables.
2 The system groups the resulting rows using the GROUP BY columns.
3 Teradata Database evaluates the ordered analytical functions on the grouped table.
4 The system applies the QUALIFY clause to the resulting set.
Teradata Database-specific functions such as CSUM and MAVG that are invoked in both the select list and in the search condition of the QUALIFY clause are computed on the grouped rows without eliminating any rows and then the system applies the search condition of the QUALIFY clause.
For window functions, such as SUM and AVG, the GROUP BY collapses all rows with the same value for the group-by columns into a single row.
QUALIFY Error Conditions
Tables referenced in a QUALIFY clause must be specified in one of the following:
Otherwise, the system returns an error to the requestor. QUALIFY is similar to HAVING in that rows are eliminated based on the value of a function computation. With QUALIFY, rows are eliminated based on the computation of the ordered analytical functions.
Example : Using the RANK Function in a QUALIFY Clause
The following statement displays each item in a sales table, its total sales, and its rank within the top 100 selling items:
SELECT itemid, sumprice, RANK() OVER (ORDER BY sumprice DESC)
FROM (SELECT a1.item_id, SUM(a1.sale)
FROM sales AS a1
GROUP BY a1.itemID) AS t1 (item_id, sumprice)
QUALIFY RANK() OVER (ORDER BY sum_price DESC) <=100;
Example : Reporting the Bottom Percentile of Items Using QUANTILE in a QUALIFY Clause
The following example reports the bottom percentile of items by profitability:
SELECT item, profit, QUANTILE(100, profit) AS percentile
FROM (SELECT item, SUM(sales)-(COUNT(sales)*items.itemcost)
AS profit
FROM daily_sales, items
WHERE daily_sales.item = items.item
GROUP BY item) AS itemprofit
QUALIFY percentile = 99;
The results of this query might look something like the following table.
|
item |
profit |
percentile |
|
Carrot-flavored ice cream |
10.79 |
99 |
|
Low fat carrot-flavored ice cream |
- 100.55 |
99 |
|
Low fat sugar-free carrot-flavored ice cream |
- 1,110.67 |
99 |
|
Regular broccoli-flavored ice cream |
- 2,913.88 |
99 |
|
Low fat broccoli-flavored ice cream |
- 4,492.12 |
99 |
Example : Behavior of OLAP Aggregate Functions That Return Zeroes
When you specify an ordered analytical aggregate function in the search condition of a QUALIFY clause, it can return a result of 0.
When there are no values to aggregate, ordered analytical aggregate functions return a 0 instead of a null.
This example first shows the rows in the demogr table that are used to calculate the ordered analytical aggregate result for the examples of current behavior that follow.
The statement returns 12 rows with valueless aggregate rows reported with zeroes rather than nulls in the Remaining Count(inc) column.
SELECT line, da, mon, inc, COUNT(inc) OVER(PARTITION BY mon
ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING)
FROM demogr
WHERE yr = 94
AND mon < 13
AND da < 10;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
line da mon inc Remaining Count(inc)
------ ------ ---- -------------------- --------------------
4803 3 1 234737.37 0
3253 2 1 ? 1
625 4 2 46706.97 0
3853 3 4 282747.07 0
3687 1 5 172470.52 0
547 9 5 31848.56 1
2292 6 7 170411.66 0
5000 8 9 40548.61 0
3213 8 9 257858.55 1
3948 6 10 217091.30 0
2471 1 10 121299.65 1
1496 7 12 ? 0
This example shows the current behavior of a qualify clause over demogr using the ordered analytical COUNT function. The query again returns 12 rows, with zeroes in place of nulls in the Remaining Count(inc) column.
SELECT line, da, mon, inc, COUNT(inc) OVER(PARTITION BY mon
ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING)
FROM demogr
WHERE yr = 94
AND mon < 13
AND da < 10
QUALIFY COUNT(inc) OVER(PARTITION BY mon ORDER BY mon, line
ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING) < 3 ;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
line da mon inc Remaining Count(inc)
------ ------ ---- -------------------- --------------------
4803 3 1 234737.37 0
3253 2 1 ? 1
625 4 2 46706.97 0
3853 3 4 282747.07 0
3687 1 5 172470.52 0
547 9 5 31848.56 1
2292 6 7 170411.66 0
5000 8 9 40548.61 0
3213 8 9 257858.55 1
3948 6 10 217091.30 0
2471 1 10 121299.65 1
1496 7 12 ? 0
This query returns the rows from the demogr table used to calculate the ordered analytical aggregate result for the examples that show the old style of returning results of an ordered analytical aggregate function specified in the search condition of a QUALIFY clause.
By using this method, valueless aggregates are returned as nulls in the Remaining Sum(1) column rather than as zeroes.
SELECT line, da, mon, inc, SUM(1) OVER(PARTITION BY mon
ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING)
FROM demogr
WHERE yr = 94
AND mon < 13
AND da < 10;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
line da mon inc Remaining Sum(1)
------ ------ ---- -------------------- ----------------
4803 3 1 234737.37 ?
3253 2 1 ? 1
625 4 2 46706.97 ?
3853 3 4 282747.07 ?
3687 1 5 172470.52 ?
547 9 5 31848.56 1
2292 6 7 170411.66 ?
5000 8 9 40548.61 ?
3213 8 9 257858.55 1
3948 6 10 217091.30 ?
2471 1 10 121299.65 1
1496 7 12 ? ?
This example shows how to use a ordered analytical SUM(1) function as a workaround to return nulls in the result instead of zeroes. The query again returns 12 rows, but reports valueless aggregate rows as nulls rather than zeroes for the Remaining Sum(1) column.
SELECT line, da, mon, inc, SUM(1) OVER(PARTITION BY mon
ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING)
FROM demogr
WHERE yr = 94
AND mon < 13
AND da < 10
QUALIFY SUM(1) OVER(PARTITION BY mon ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING) < 3;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
line da mon inc Remaining Sum(1)
------ ------ ---- -------------------- ----------------
4803 3 1 234737.37 ?
3253 2 1 ? 1
625 4 2 46706.97 ?
3853 3 4 282747.07 ?
3687 1 5 172470.52 ?
547 9 5 31848.56 1
2292 6 7 170411.66 ?
5000 8 9 40548.61 ?
3213 8 9 257858.55 1
3948 6 10 217091.30 ?
2471 1 10 121299.65 1
1496 7 12 ? ?
This example shows how to use NULLIFZERO with the ordered analytical COUNT function as a workaround to return nulls in the result instead of zeroes.
SELECT line, da, mon, inc, NULLIFZERO(COUNT(inc)
OVER(PARTITION BY mon
ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING)
FROM demogr
WHERE yr = 94
AND mon < 13
AND da < 10
QUALIFY NULLIFZERO(COUNT(inc)OVER(PARTITION BY mon
ORDER BY mon, line
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING) < 3;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
line da mon inc Remaining Count(inc)
------ ------ ---- -------------------- --------------------
4803 3 1 234737.37 ?
3253 2 1 ? 1
625 4 2 46706.97 ?
3853 3 4 282747.07 ?
3687 1 5 172470.52 ?
547 9 5 31848.56 1
2292 6 7 170411.66 ?
5000 8 9 40548.61 ?
3213 8 9 257858.55 1
3948 6 10 217091.30 ?
2471 1 10 121299.65 1
1496 7 12 ? ?
For More Information
For more information related to the QUALIFY clause, see: