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/case3.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

Okay, that’s the boring bit out of the way and it’s time to turn to the first example. All of the examples included are designed to work against the sample SH (sales history) schema that has been available since Oracle 9i. I selected this because 

• It contains a reasonable volume of data, including the 900,000+ row SALES table.
• I think it’s a fair reflection of a business application. 
• It has a standard published definition and sensible table and column names. Full documentation for the schema is available in the Sample Schemas manual in the generic documentation set. This means that you can create the same schema (if it’s not already loaded into your database), test the examples and play around with different approaches. 

Example 1 illustrates the way in which DECODE was often used to improve report formatting. 

Example 1

SELECT cust_id, cust_first_name, cust_last_name,
CASE cust_gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'UNKNOWN'
END gender
FROM customers
WHERE ROWNUM < 6;

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME                           GENDER
---------- -------------------- ---------------------------------------- ------
     49671 Abigail              Ruddy                                    Male
      3228 Abigail              Ruddy                                    Male
      6783 Abigail              Ruddy                                    Male
     10338 Abigail              Ruddy                                    Male
     13894 Abigail              Ruddy                                    Male

The ROWNUM test limits the output for the example because there are 55,500 customers! That’s one aspect of the new sample schemas that can make them harder to work with than the old EMP and DEPT – sometimes you only want a small output example. 

Although translating code values into readable descriptions in reporting applications is the most common and obvious use of DECODE (particularly given the name of the function) and CASE, it masks some of the more powerful general functionality which I’ll turn to next. 

Previous