| Home
Page
Logic-dependent Aggregation (contd) |
Please note this page has moved to http://oracledoug.com/case7.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-) Pivot Tables and Multi-part LogicA common use of CASE is to generate pivot tables or cross-matrix reports (although the new MODEL clause in 10g is more powerful). We might want to modify our previous report to just display sales information for Photo Products but to have one column per month for the last quarter of 2001.
To achieve this, we’ll select the usual product and sales data, group on the product category and sub-category and apply the 10% mark-up to Photo products. However, we’ll also check which month the sale occurred in before adding the amount sold to a given column, one for each month. It’s important to remember here that if there is no ELSE clause and none of the conditions is TRUE, the default value of NULL will be returned, so the running SUM for that month will be unaffected. That’s the approach I’ve used here and the behaviour has been consistent over multiple versions of Oracle, but if you’re cautious, you can simply add ELSE 0 or ELSE NULL to each CASE expression.
Example 3a (Note that this is the first example to use a Searched CASE expression where we have a number of WHEN clauses containing discrete logical tests, rather than comparing each to the same initial test expression. This can be clearly identified because the first WHEN keyword appears immediately after the CASE keyword.) One of the problems with DECODE is that writing multi-part logical expressions using the AND operator can be a little cumbersome as each AND operation would require an additional nested DECODE so, although example 3a might look long-winded and slightly difficult to follow (imagine if the report covered 18 months, rather than 3), the DECODE version would be worse! You’re likely to find CASE expressions much easier to work with.
However, I’ve just fallen into a common trap when using CASE. The logic it allows us to implement is so flexible that it can encourage us to produce logically consistent but inefficient code if we're not careful. The thing to keep in mind is that when you use CASE in the SELECT list, it is a post-retrieval function. What this example will do is trawl through all of the sales figures, then apply a DECODE function to it in such a way as to exclude most sales from the result, because they didn’t occur in the last quarter of 2001.
Another way of looking at this is that we are using CASE expressions in the SELECT clause to eliminate results that should have been eliminated much earlier, in the WHERE clause. Why retrieve data that we know we are going to discard subsequently! After all, that’s the whole point of these techniques, to reduce the workload required to produce the reports
A better way of achieving the same result is shown in Example 3b.
Example 3b SELECT p.prod_category, p.prod_subcategory, SUM(CASE WHEN p.prod_category = 'Photo' AND t.calendar_month_number = 10 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 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 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 / The additional lines in the WHERE clause, shown in bold text will ensure that we reduce the volume of data that we’re processing to the minimum first. We’re not interested in any sales data that doesn’t occur in the last quarter of 2001, so let’s not even bother selecting it and, given that we’ve just eliminated the data that we’re not interested in, there’s no need to check the year in the CASE expressions any more. The interesting thing is that the optimiser will choose the same execution plan for both of these queries and so any performance gain is minimal. However, it’s a useful principle when writing SQL statements to eliminate as much data as possible as early as possible – with the most selective WHERE clause. This gives the optimiser the best chance of choosing an efficient access path and reduces the resource requirements. In many cases the difference between having logic in the WHERE clause instead of the SELECT clause will be dramatic because Oracle will be able to use an index to retrieve a smaller amount of data more quickly. The golden rule is Use the WHERE clause to eliminate all unnecessary data first and then use CASE in the SELECT list for additional processing. |