What is Difference between Decode and Case

Decode:
1.It’s a function
2.can compare only discrete vaules
3.Pl/Sql standards
4.cannot process null

Case:
1.It's an Expression
2.can hande range values
3.ANSI Standards
4.Processing time is faster when compared to Decode
5.can process null


Decode
The default operator for comparison is '=',
for e.g select deptno,decode
(deptno,10,'dept10',20,dept20,default)

so if the deptno=10 then the value is replaced by dept10
and so on,

whereas in Case:

we can use any operator other than '=',
for e.g
select deptno,
case when deptno=10 and sal>1000 then 'dept10'
else
'dept20'




Decode: using for reporting purpose. and it is implemented
by if stmt.
Ex: select decode(job,'CLERK','executive',job) from emp;

Case : implemented by if & elsif condition. it is using
pl/sql equality conditionality.
Ex : select case job='CLERK' then 'executive' else 'no' end
a from emp;

it is also used for multipul colms & multi conditions.

in above stmt a is reffered alias name.




There is one more Important difference between CASE and DECODE

DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure

Eg:-


Code: [Select all] [Show/ hide]SQL> create or replace procedure pro_01(n number) is
2 begin
3 dbms_output.put_line(' The number = '||n);
4 End;
5 /

Procedure created.

SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
2 :a := 'ONE';
3 End;
4 /

PL/SQL procedure successfully completed.

SQL> Begin
2 pro_01(Decode(:a,'ONE',1,0));
3 End;
4 /
pro_01(Decode(:a,'ONE',1,0));
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored


SQL> Begin
2 pro_01(case :a when 'ONE' then 1 else 0 end);
3 End;
4 /
The number = 1

PL/SQL procedure successfully completed.


Ur's
AmarAlam