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 zeros 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;
Result:
*** 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 zeros 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 ;
Result:
*** 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 zeros.
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;
Result:
*** 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 zeros. The query again returns 12 rows, but reports valueless aggregate rows as nulls rather than zeros 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;
Result:
*** 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 zeros.
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;
Result:
*** 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 ? ?