The new Oracle9i CASE statement   Donald K. Burleson

The case statement is a more flexible extension of the age-old decode statement, which dates back to the earliest releases of Oracle. The decode statement is used to translate one value to another, which is quite handy.  Unfortunately, the syntax of decode is very cryptic:

select

   decode (

      column_name,

      col_val1, translated_val1,

      col_val2, translated_val2,

      col_val3, translated_val3,

      col_val4, translated_val4,

     not_in_list_default

)

from . . . ;

The Oracle9i CASE statement has far simpler syntax:

SELECT

   last_name,

   state_code,
(CASE state_code
      WHEN ‘AL’ THEN ‘Alabama’
      WHEN ‘AK’ THEN ‘Alaska’
      WHEN ‘AR’ THEN ‘Arkansas’

      WHEN . . . .  
      ELSE ‘N/A’
END ) state_name
FROM

   employees

ORDER BY

   last_name;

This syntax is far more elegant than the complex decode statement, and also more powerful than decode.  A more complex version is the CASE expression where multiple comparisons can be used to find a match:

SELECT last_name, job_id, salary,
  (CASE
    WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%'
    WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
    WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
    WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
    ELSE 'NOT APPLICABLE'
  END ) Raise
FROM employees;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.