question

dbkid avatar image
dbkid asked

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?

sqloracleplsql
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
KillerDBA avatar image
KillerDBA answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dbkid avatar image dbkid commented ·
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.
0 Likes 0 ·
BI DWH BALA avatar image
BI DWH BALA answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.