INTERVIEW QUESTIONS DATABASE SQL DETAILS

Question: Difference between decode and case.
in which case we are using case and in which case we are using decode?
with an example.

Answer: 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

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 Source: CoolInterview.com

Using CASE expression we can use all comparative operators (<,>,==, etc), where as using DECODE we should always use = condition. Source: CoolInterview.com

Decode is the sql functions for the replacement of If- Then -else logic and case is similar to
If-Then-else logic except that we can make logical comparison of columns involved in the case structures.
Ex: select case snum when snum > 10 then 'High' when snum>5 then 'Low' end from sales..
Ex: select decode(snum,10,'high',5,'low') from sales... that is we cannot make logical comparison of columns in Decode() functions.
Source: CoolInterview.com

A DECODE FUNCTION ALWAYS TAKES
MINIMUM OF 4 ARGUMENTS
DECODE<N1,N1,N3,N4>
IF N1=N2
THEN RETURN N3
ELSE
RETURN N4 Source: CoolInterview.com

First Decode:
In Decode we can use less words and it will make easy to handle..

Case:
IN case condition It uses 'When' And 'Then' .So this will confuse in a big queries.

So i prefer decode is better than Case... Source: CoolInterview.com

Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional statement.
Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because 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). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

Example with DECODE function
Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function:

select
decode (
region,
‘N’,’North’,
‘S’,’South’,
‘E’,’East’,
‘W’,’West’,
‘UNKNOWN’
)
from
customer;
Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

Example with CASE statement

select
case
region
when ‘N’ then ’North’
when ‘S’ then ’South’
when ‘E’ then ’East’,
when ‘W’ then ’West’
else ‘UNKNOWN’
end
from
customer;
Difference between DECODE and CASE:
Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Following is the list of differences -
1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
3. CASE expects datatype consistency, DECODE does not.
4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
5. CASE executes faster in the optimizer than does DECODE.
6. CASE is a statement while DECODE is a function. Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question