DECODE versus CASE

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

CASE is more elegant.

SELECT
  DECODE
  (
    size_code,
    1,  'Small',
    2,  'Medium',
    3,  'Large',
        '?'
  ) AS size
FROM    orders;

SELECT
  CASE
    size_code
    WHEN  1  
      THEN  'Small'
    WHEN  2  
      THEN  'Medium'
    WHEN  3
      THEN  'Large'
    ELSE  '?'
  END  AS size
FROM  orders;

SELECT
  CASE
    WHEN size_code = 1
      THEN 'Small'
    WHEN size_code = 2
      THEN 'Medium'
    WHEN size_code = 3
      THEN 'Large'
    ELSE '?'
  END  AS size
FROM    orders;

The example above illustrates the type of job DECODE was designed to do: translating discrete values from a single column in one coding scheme (1, 2, 3) to de-coded values ('Small', 'Medium', 'Large'). The example above also illustrates the syntax used in the two different forms of the CASE statement: the simple CASE (closer to DECODE) and searched CASE. All three versions produce the same results. The example above does not illustrate the enormous potential of the CASE statement.

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

Continue to the next example...