Series: Subject: Topic:
Question: 288 of 429

difference between decode and case.
in which case we are using case and in which case we are using decode?
with an example.

Asked by: Interview Candidate | Asked on: Jul 17th, 2006
Showing Answers 1 - 11 of 11 Answers

Answered On : Jul 19th, 2006

First I will give one example using 'decode'
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

Login to rate this answer.

Answered On : Aug 1st, 2006

  SELECT DECODE(DEPTNO,10,'TEN',20,'TWANTY',30,'THIRTY',40,'FORTY','OTHER') FROM DEPTSELECT CASE DEPTNO   when  10 then 'TEN'                     when  20 then 'TWANTY'                     when  30 then 'THIRTY'                     when  40 then 'FORTY'              ELSE    'OTHER' END FROM DEPTDifference between CASE and DECODE1) Decode is a function while Case statement.2) Case cannot process null while decode can.

Login to rate this answer.

Decode                                                                  functionwe will use this one in sql                                      we will use this one plsqlit will accept null values                                        it will not accept the null

Yes  2 Users have rated as useful.
Login to rate this answer.

Answered On : Aug 28th, 2006

One of the measure diff. between decode and case statement :->Decode does not evaluate inequality operator Where as case can do.

Login to rate this answer.

Answered On : Oct 27th, 2006

Why cant we use CASE for checking nullsselect case when col1 is null then 'Heyitisnull' else col1 end from case;

Login to rate this answer.

Decode is a function where case is expression .decode gives result different when using null  see belowSQL> 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 dualSQL> /CASENULLWHENNULLTHEN1ELSE0END-----------------------------                            0but see here SQL> select case when null is null then 1 else 0 end from dual;CASEWHENNULLISNULLTHEN1ELSE0END-------------------------------                              1

Login to rate this answer.

Answered On : 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.

Yes  1 User has rated as useful.
Login to rate this answer.

Answered On : 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/procedureDECODE 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. 

Login to rate this answer.

Answered On : Jul 14th, 2009

View all answers by jaspreet.nagra

Hi Murali, You can also use Decode in where clause:select * from dualwhere decode( null, null, 1, 0)=1;

Login to rate this answer.

Answered On : Nov 17th, 2009

View all answers by joyfun23

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

Yes  1 User has rated as useful.
Login to rate this answer.

Answered On : Dec 21st, 2009

View all answers by Sirajuddin84

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.

Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions



twitter fb Linkedin GPlus RSS


Interview Question

 Ask Interview Question?


Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.