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

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'
        ELSE 'UNKNOWN'
    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.


Improving SQL efficiency using CASE