Tables and Multi-part Logic
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
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.
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.
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
WHERE ROWNUM < 6;
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.