Decode and Case functions (Oracle)

Asked By sushmit 110 points N/A Posted on - 04/28/2011

Hi,

I have been working on Oracle for quite sometime now. I have always used the DECODE function that represents IF-THEN-ELSE clause. I read somewhere that from Oracle 8i onwards CASE statement has been introduced to get the same result. But which one out of the two is better considering both are used to do the same task?

 

• Status: Open
• Question Views: 1262
• Answer Count: 5
• Vote Up 11 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by DiyaSharma

Go To Solution

Best Answer

Best Answer

Answered By DiyaSharma 0 points N/A #89175

Decode and Case functions (Oracle)

You are right when you say that both DECODE and CASE achieve the same functionality. But then there are certain situations where CASE is better than DECODE. It is cleaner as in easier to read and comprehend. Consider the following syntax to understand.

(case when obj='VIEW' then 1

      when obj='PROCEDURE' then 1

      else 0)

 

A similar query using DECODE would look something like this.

 

Decode( obj, 'VIEW', 1, 'PROCEDURE', 1, 0 ) 

About DiyaSharma

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By sushmit 110 points N/A #89176

Decode and Case functions (Oracle)

Thanks for your inputs. Yesterday while comparing DECODE and CASE at one point I found that DECODE is better than CASE. Consider the following snippet:

Select

Case when null=null then 'true' else 'false' end from dual ;

 

Select

Decode(null,null,'true','false') from dual ;

 

The output from the first statement is: False

 

From second statement : true

About sushmit

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By DiyaSharma 0 points N/A #89177

Decode and Case functions (Oracle)

Nice observation. You can equate nulls in DECODE but not in CASE. But there is a work around by which you can get the same result. When you use IS NULL instead of =NULL you get the same result with CASE. Check the following query :

Select

Case when null is null then 'true' else 'false' end from dual ;

About DiyaSharma

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By sushmit 110 points N/A #89178

Decode and Case functions (Oracle)

The case syntax includes ELSE clause, is there any ELSE clause for DECODE statement?

About sushmit

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By DiyaSharma 0 points N/A #89179

Decode and Case functions (Oracle)

No. DECODE doesn't have an ELSE clause. Its syntax is:

DECODE(expression,search,result[,search,result]…..[,default])

About DiyaSharma

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database