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!

Next

Improving SQL efficiency using CASE