Q
Problem solve Get help with specific problems with your technologies, process and projects.

DECODE versus CASE

What is the difference between DECODE and CASE? Please give some examples.

CASE is more elegant.

DECODE Simple CASE Searched CASE
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;
Available in Oracle 5 (or earlier) Available in Oracle 9.0 Available in Oracle 8.1
Compares 1st argument with 2nd, 4th, ... argument and, if equal, returns 3rd, 5th, ... argument Compares 1st value with successive WHEN-values and, if equal, returns corresponding THEN-value Evaluates successive WHEN-conditions and, if true, returns corresponding THEN-value

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


This was last published in August 2003

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close