BALAJI

• Jul 19th, 2006

 

First I will give one example using 'decode'


sql>SELECT ENAME,SAL,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','OTHERS') "DEPARTMENTS" FROM EMP;


I have used the decode function on 'deptno' column. It will give the user-friendly output. instead of using 'accounting', 'research'.. We can use anything we want to get the friendly outputs.


I have to check-out the 'case' function after that I will give an example

Manohar

• Aug 1st, 2006

 

 

SELECT DECODE(DEPTNO,10,'TEN',20,'TWANTY',30,'THIRTY',40,'FORTY','OTHER') FROM DEPT

SELECT CASE DEPTNO   when  10 then 'TEN'
                     when  20 then 'TWANTY'
                     when  30 then 'THIRTY'
                     when  40 then 'FORTY'
              ELSE    'OTHER' END FROM DEPT

Difference between CASE and DECODE

1) Decode is a function while Case statement.

2) Case cannot process null while decode can.

dmadhusudhanarao

 Profile Answers by dmadhusudhanarao 

• Aug 2nd, 2006

 

Decode                                                                  function

we will use this one in sql                                      we will use this one plsql

it will accept null values                                        it will not accept the null

Rai

• Aug 28th, 2006

 

One of the measure diff. between decode and case statement :->

Decode does not evaluate inequality operator Where as case can do.

Gautam

• Oct 27th, 2006

 

Why cant we use CASE for checking nulls

select case when col1 is null then 'Heyitisnull' else col1 end from case;

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Feb 18th, 2007

 

decode is a function where case is expression .
decode gives result different when using null  see below

SQL> select decode( null,null,1,0) from dual;

DECODE(NULL,NULL,1,0)
---------------------
                    1

  1* select case null when null then 1 else 0 end from dual
SQL> /

CASENULLWHENNULLTHEN1ELSE0END
-----------------------------
                            0
but see here
SQL> select case when null is null then 1 else 0 end from dual;

CASEWHENNULLISNULLTHEN1ELSE0END
-------------------------------
                              1

Murali

• Aug 13th, 2007

 

Hi,

The Main Difference is that
 1) Decode cannot be used in Where clause but Case can.
 
2)  In Decode Else can be specifed in the statement it self but in Case a seperate statement has to be written.

g_sidhu

 Profile 

• Feb 7th, 2008

 

• DECODE can be used Only inside SQL statement, But CASE can be used any where even as a parameter of a function/procedure
• 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).
• CASE is almost always easier to read and understand, and therefore it's easier to debug and maintain.
• Another difference is, CASE is an ANSI standard, where as Decode is proprietary for Oracle.
• Performance wise there is not much differences. But Case is more powerful than Decode.

 

jaspreet.nagra

 Profile Answers by jaspreet.nagra 

• Jul 14th, 2009

 

Hi Murali,

You can also use Decode in where clause:

select * from dual
where decode( null, null, 1, 0)=1;

joyfun23

 Profile Answers by joyfun23 

• Nov 17th, 2009

 

select 'RAGHAV' from dual where dummy =(
select CASE when 'A' ='A'

then 'X' else 'Y'
end from dual
)


CASE Works in where clause and Decode too!!

Sirajuddin84

 Profile Answers by Sirajuddin84 

• Dec 21st, 2009

 

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.