| Home
Page
Back
to Papers
Introduction
Definitions
Basic
Usage
Logic-dependent
Aggregation
An
Interlude
Logic-dependent
Aggregation (contd)
Pivot
Tables and Multi-part Logic
Beyond
Equality
Conclusion
Additional
Resources
|
Please note this page has moved to http://oracledoug.com/case8.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)
Beyond Equality
All of the examples so far have used simple equality tests. This is the limit of what the DECODE function can do. (There are workarounds to this using the SIGN, GREATEST or LEAST functions, for example – see the original DECODE paper for details).
However, CASE Expressions allows us to mix and match conditional tests on different combinations of columns, literal values and operators. For example, the sales manager might like to see the previous report modified so that the 10% markup is only applied to sales where the amount_sold is between 1000 and 2000
Example 3c
SELECT p.prod_category, p.prod_subcategory,
SUM(CASE WHEN p.prod_category = 'Photo' AND t.calendar_month_number = 10
AND s.amount_sold BETWEEN 1000 AND 2000
THEN amount_sold *1.1
WHEN p.prod_category != 'Photo' AND t.calendar_month_number = 10
THEN amount_sold
END) AS OCT_dollars,
SUM(CASE WHEN p.prod_category = 'Photo' AND t.calendar_month_number = 11
AND s.amount_sold BETWEEN 1000 AND 2000
THEN amount_sold *1.1
WHEN p.prod_category != 'Photo' AND t.calendar_month_number = 11
THEN amount_sold
END) AS NOV_dollars,
SUM(CASE WHEN p.prod_category = 'Photo' AND t.calendar_month_number = 12
AND s.amount_sold BETWEEN 1000 AND 2000
THEN amount_sold *1.1
WHEN p.prod_category != 'Photo' AND t.calendar_month_number = 12
THEN amount_sold
END) AS DEC_dollars
FROM sales s, times t, products p
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND t.calendar_year = 2001
AND t.calendar_month_number BETWEEN 10 AND 12
GROUP BY p.prod_category, p.prod_subcategory
ORDER BY 1, 2
/
PROD_CATEGORY PROD_SUBCATEGORY OCT_DOLLARS NOV_DOLLARS DEC_DOLLARS
---------------- -------------------- ----------- ----------- -----------
Electronics Game Consoles 105,876.22 88,989.00 193,546.85
Home Audio 220,133.02 246,483.07 252,605.84
Y Box Accessories 14,769.55 15,101.27 12,499.64
Y Box Games 52,945.94 55,197.39 45,690.73
Hardware Desktop PCs 153,857.16 239,435.84 180,910.18
Portable PCs 192,513.25 201,927.44 205,868.81
Peripherals and Accessories 65,193.15 53,754.82 69,484.05
Accessories
CD-ROM 63,934.53 65,239.50 37,439.03
Memory 126,492.82 116,220.35 120,243.47
Modems/Fax 85,488.22 75,540.22 82,163.87
Monitors 338,795.95 309,261.00 318,457.99
Printer Supplies 126,890.11 103,908.88 98,610.61
Photo Camcorders 276,726.14 302,757.06 309,163.10
Camera Batteries
Camera Media
Cameras 60,808.07 59,295.81 67,030.02
Software/Other Accessories 60,090.11 46,264.68 50,513.14
Bulk Pack Diskettes 8,816.24 6,497.00 7,066.86
Documentation 63,202.96 67,330.54 78,742.43
Operating Systems 89,284.76 106,532.85 96,790.97
Recordable CDs 22,849.65 20,342.21 26,734.34
Recordable DVD Discs 74,207.23 57,712.70 60,317.69
Hold on a minute. There’s something wrong with the results for Camera Batteries and Camera Media. There aren’t any. The problem here is that up until now I’ve been relying on the default value of NULL being returned if none of the conditions is true, so NULL will be added to the total, having no effect. (N.B this is subtly different behaviour to how NULL affects an addition operation, for example. A NULL value in a SUM operation will not force the result to be NULL, it will effectively be ignored.) However because the amount_sold for ‘Camera Batteries’ is not between 1000 and 2000 for any of the three months (so the first condition fails) but they are ‘Photo’ products (so the second condition fails) NULL will be added to the total repeatedly, with the end result of NULL. If what we really want to do is show a value of zero, then we need to add an ELSE clause to each of the CASE expressions, as follows.
Example 3d
SELECT p.prod_category, p.prod_subcategory,
SUM(CASE WHEN p.prod_category = 'Photo' AND t.calendar_month_number = 10
AND s.amount_sold BETWEEN 1000 AND 2000
THEN amount_sold *1.1
WHEN p.prod_category != 'Photo' AND t.calendar_month_number = 10
THEN amount_sold
ELSE 0
END) AS OCT_dollars,
SUM(CASE WHEN p.prod_category = 'Photo' AND t.calendar_month_number = 11
AND s.amount_sold BETWEEN 1000 AND 2000
THEN amount_sold *1.1
WHEN p.prod_category != 'Photo' AND t.calendar_month_number = 11
THEN amount_sold
ELSE 0
END) AS NOV_dollars,
SUM(CASE WHEN p.prod_category = 'Photo' AND t.calendar_month_number = 12
AND s.amount_sold BETWEEN 1000 AND 2000
THEN amount_sold *1.1
WHEN p.prod_category != 'Photo' AND t.calendar_month_number = 12
THEN amount_sold
ELSE 0
END) AS DEC_dollars
FROM sales s, times t, products p
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND t.calendar_year = 2001
AND t.calendar_month_number BETWEEN 10 AND 12
GROUP BY p.prod_category, p.prod_subcategory
ORDER BY 1, 2
/
PROD_CATEGORY PROD_SUBCATEGORY OCT_DOLLARS NOV_DOLLARS DEC_DOLLARS
---------------- -------------------- ----------- ----------- -----------
Electronics Game Consoles 105,876.22 88,989.00 193,546.85
Home Audio 220,133.02 246,483.07 252,605.84
Y Box Accessories 14,769.55 15,101.27 12,499.64
Y Box Games 52,945.94 55,197.39 45,690.73
Hardware Desktop PCs 153,857.16 239,435.84 180,910.18
Portable PCs 192,513.25 201,927.44 205,868.81
Peripherals and Accessories 65,193.15 53,754.82 69,484.05
Accessories
CD-ROM 63,934.53 65,239.50 37,439.03
Memory 126,492.82 116,220.35 120,243.47
Modems/Fax 85,488.22 75,540.22 82,163.87
Monitors 338,795.95 309,261.00 318,457.99
Printer Supplies 126,890.11 103,908.88 98,610.61
Photo Camcorders 276,726.14 302,757.06 309,163.10
Camera Batteries 0.00 0.00 0.00
Camera Media 0.00 0.00 0.00
Cameras 60,808.07 59,295.81 67,030.02
Software/Other Accessories 60,090.11 46,264.68 50,513.14
Bulk Pack Diskettes 8,816.24 6,497.00 7,066.86
Documentation 63,202.96 67,330.54 78,742.43
Operating Systems 89,284.76 106,532.85 96,790.97
Recordable CDs 22,849.65 20,342.21 26,734.34
Recordable DVD Discs 74,207.23 57,712.70 60,317.69
That’s better!
Previous
|