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 10.1.0.4.0, 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
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
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.