Home Page

Back to Papers



Basic Usage

Logic-dependent Aggregation

An Interlude

Logic-dependent Aggregation (contd)

Pivot Tables and Multi-part Logic

Beyond Equality


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 ;-)

Basic Usage

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. 
I ran the examples against Oracle, but you should find identical results on any version of 9i or 10g. (Iíd be extremely interested in any variations you might come across.) Iíve used the cost-based optimiser and the execution plans are generated using the SQL*Plus Autotrace facility. 

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'
END gender
FROM customers

   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

This statement checks the cust_gender column of the customers table and if the value = ĎMí, then it returns 'Male' or if it's ĎFí it returns ĎFemaleí. Iíve included a default clause that displays ĎUNKNOWNí if itís not one of the two expected values. 

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. 



Technical Papers Utilities and Scripts Book Reviews Links
My ResumeFun & GamesEmailHome