Monday, December 19, 2011

Decode vs Case

Example for Decode and Case:

SQL> SELECT ename,
  2         DECODE(deptno, 10, 'ACCOUNTING',
  3                        20, 'RESEARCH',
  4                        30, 'SALES',
  5                        40, 'OPERATIONS',
  6                            'UNKNOWN') AS department
  7    FROM emp
  8   WHERE rownum < 4
  9  /

SQL> SELECT ename,
  2         CASE deptno
  3           WHEN 10 THEN 'ACCOUNTING'
  4           WHEN 20 THEN 'RESEARCH'
  5           WHEN 30 THEN 'SALES'
  6           WHEN 40 THEN 'OPERATIONS'
  7           ELSE
  8                        'UNKNOWN'
  9         END AS department
 10    FROM emp
 11   WHERE rownum < 4
 12  /

Difference:
1.
DECODE works with expressions which are scalar values.
CASE can work with predicates and subqueries in searchable form

SQL> select ename,
  2   case
  3     when ename in ('KING','SMITH','ALLEN') then
  4        'Managers'
  5     when exists (select 1 from dept where deptno = emp.deptno and deptno = 10) then
  6        'Guy from 10th'
  7     else
  8        'Another person'
  9   end blah_blah
 10  from emp  
 11  /

2.
CASE executes faster in the optimizer than does DECODE.

3.
DECODE will return "true" if you compare NULL to NULL. CASE will not.

DECODE(NULL, NULL, 1, 0)
will return '1'.

CASE NULL
    WHEN NULL THEN 1
    ELSE 0
END
will return '0'.

You would have to write it as:
CASE
    WHEN NULL IS NULL THEN 1
    ELSE 0
END
will return '1'

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

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

1 comment: