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
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
This comment has been removed by the author.
ReplyDelete