SQL - difference between decode and case.in which case we are using case and in which case we are using de

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 91 of 171

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

Total Answers and Comments: 10

Last Update: November 17, 2009 Asked by: Rashmi Das

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

July 19, 2006 04:36:33

BALAJI

RE: difference between decode and case.in which ...

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

Is this answer useful? Yes | No

August 01, 2006 05:11:56

Manohar

RE: difference between decode and case.in which ...

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.

Is this answer useful? Yes | No

August 02, 2006 10:31:36

dmadhusudhanarao

Member Since: August 2006 Contribution: 7

RE: difference between decode and case.in which ...

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

Is this answer useful? Yes | No

August 28, 2006 08:01:02

Rai

RE: difference between decode and case.in which ...

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

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

Is this answer useful? Yes | No

October 27, 2006 06:06:39

Gautam

RE: difference between decode and case.in which ...

Why cant we use CASE for checking nulls

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

Is this answer useful? Yes | No

February 18, 2007 10:10:13

ddkdhar

Member Since: June 2006 Contribution: 48

RE: difference between decode and case.in which ...

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

Is this answer useful? Yes | No

0 1

Overall Rating: -1

August 13, 2007 01:35:56

Murali

RE: difference between decode and case.in which ...

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.

Is this answer useful? Yes | No

0 1

Overall Rating: -1

February 07, 2008 11:32:15

g_sidhu

Member Since: August 2007 Contribution: 122

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

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.

Is this answer useful? Yes | No

0 1

Overall Rating: -1

July 14, 2009 02:30:46

jaspreet.nagra

Member Since: July 2009 Contribution: 3

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

Hi Murali

You can also use Decode in where clause:

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

Is this answer useful? Yes | No

0 1

Overall Rating: -1

November 17, 2009 04:07:16

#10

joyfun23

Member Since: November 2009 Contribution: 2

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

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

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank