Overview: CASE is better than DECODE, because in DECODE we can only perform equality (” =”) comparison and <, > comparisons are not possible.
CASE: Simple CASE (9i), Searched CASE (8i)…Check the lat scenario to find the difference between Simple n Searched CASE.
What is the diff between case and decode, and which one is better and why?
The “simple” CASE expression allows any kind of condition.
DECODE is like the “searched” CASE: all the conditions are of the form n=x, where n is the same throughout the expression.
Anything that you can do in CASE you can also do in DECODE, and vice-versa. Because of the limitation mentioned above, doing complicated things in DECODE often involves convoluted logic, obscure functions, and nested DECODEs.
In some very simple situations, DECODE is shorter and easier to understand.
In all but the simplest situations, CASE is shorter and easier to understand.
Scenario2: can any one of you give me a brief difference between case and decode statements…
CASE is as per ISQL standard where as DECODE is not.
CASE executes faster than DECODE.
Both can be used in sql as well as pl/sql
Before oracle 8i there was only DECODE function. 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.
Hope this is clear for you. Please don’t hesitate if you have some other doubts regarding this.
Scenario 3: What is the difference between DECODE and CASE? Please give some examples.
) AS size
END AS size
WHEN size_code = 1
WHEN size_code = 2
WHEN size_code = 3
END AS size
|Available in Oracle 5 (or earlier)||Available in Oracle 9.0||Available in Oracle 8.1|
|Compares 1st argument with 2nd, 4th, … argument and, if equal, returns 3rd, 5th, … argument||Compares 1st value with successive WHEN-values and, if equal, returns corresponding THEN-value||Evaluates successive WHEN-conditions a|