Difference between case and code statements in oracle?

I am wondering why we have decode in addition to Case statement. Is there any performance difference? The case statement does whatever decode does. What are the differences between case and code?

more ▼

asked Dec 17, 2009 at 08:43 PM in Default

avatar image

131 9 10 14

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I don't know if it's the only reason but CASE is in the ANSI Standard. DECODE is not but DECODE was Oracle's original solution for a CASE-type situation.

Don Burleson offers up this opinion, "As we can see, the decode function is convoluted and hard to write. Oracle added the case function to SQL starting in Oracle9i to simplify this type of data transformation." Here's a link:

Burleson Consulting on DECODE and CASE

Personally, I think CASE is a bit easier to read (unless you don't trouble to format it at all) and it might be easier to find errors in CASE than in the DECODE function.

Also, the CASE expression mirrors the CASE control statement of PL/SQL, so you can learn one syntax instead of two.

As a "non-native Oracle developer," I'm more comfortable with CASE.

more ▼

answered Dec 17, 2009 at 10:34 PM

avatar image

1.5k 9 11 14

Thanks KillerDBA. What I understood is CASE is more readable and simpler to write. And we can use it in PL/SQL. I would like to see what our friends will say any more differences.

Dec 18, 2009 at 07:25 PM dbkid
(comments are locked)
10|1200 characters needed characters left

CASE was introduced in Oracle 8.1.6 as a replacement for the DECODE . Anyway it is much better option than DECODE as it is ,

  1. More Flexible than DECODE

  2. More easier to read

  3. ANSI Compatible

  4. compatible in PL/SQL Context

more ▼

answered Dec 18, 2009 at 07:46 PM

avatar image

606 54 62 66

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 17, 2009 at 08:43 PM

Seen: 3741 times

Last Updated: Dec 22, 2009 at 12:31 PM

Copyright 2018 Redgate Software. Privacy Policy