difference between case and decode

[venkatareddy]

Hi all,
can any one of you give me a brief difference between case and decode statements...

[debasisdas]

CASE is as per ISQL standard where as DECODE is not.

CASE executes faster than DECODE.

both can be used in sql as well as pl/sql

[BHARATH RS]

Before oracle 8i there was only DECODE function. The main problem with DECODE is that we can compare only discrete values and not ranged values. So they introduced the CASE statements from oracle 8i onwards. This CASE was able to handle ranged values by using >, <, BETWEEN.

Hence the difference between the DECODE and CASE is that DECODE cannot be used handle Ranged values, whereas CASE is capleable of that.


Hope this is clear for you. Please dont hesitate if you have some other doubts regarding this.

[Medhatithi]

Before oracle 8i there was only DECODE function. The main problem with DECODE is that we can compare only discrete values and not ranged values. So they introduced the CASE statements from oracle 8i onwards. This CASE was able to handle ranged values by using >, <, BETWEEN.

Hence the difference between the DECODE and CASE is that DECODE cannot be used handle Ranged values, whereas CASE is capleable of that.


Hope this is clear for you. Please dont hesitate if you have some other doubts regarding this.

There is however, a very important point regarding decode. It automatically converts the second return value to the datatype of the first return value. And if the first return value is null, then the second return value is converted to varchar2. BE VERY CAREFUL USING DECODE FUNCTION IF THE FIRST RETURN VALUE IS NULL i.e.
max(decode(stat us,'BC',NULL,cr eate_date))
In this case, the create_date column will be converted to varchar2 type, and so
the max may give errors (we faced this issue just a month ago)
CASE-WHEN is obviously a better choice in this regard.

[abhi88920]

Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional 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.

Example with DECODE function

Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function:

select
decode (
region,
‘N’,’North’,
‘S’,’South’,
‘E’,’East’,
‘W’,’West’,
‘UNKNOWN’
)
from
customer;

Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

Example with CASE statement

select
case 
region
when ‘N’ then ’North’
when ‘S’ then ’South’
when ‘E’ then ’East’,
when ‘W’ then ’West’
else ‘UNKNOWN’
end
from
customer;

Difference between DECODE and CASE:

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Following is the list of differences -
1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
3. CASE expects datatype consistency, DECODE does not.
4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
5. CASE executes faster in the optimizer than does DECODE.
6. CASE is a statement while DECODE is a function.

Difference between decode and case statement in Oracle