The Difference Between DECODE and CASE
Decode and Case
Difference Between DECODE and CASE:- Both are used for a conditional construct in SQL.1. A CASE can work with logical operators other than ‘=’ while DECODE performs an equality check only.
Example:- To display grade based on their salaries.
SQL>select ename, case when sal < 1000 then 'A'
when (sal >=1000 and sal < 2000) then 'B'
when (sal >= 2000 and sal < 3000) then 'C'
else 'D'
end sal_grade
from emp where rownum < 6;
Output:-
ENAME SAL SAL_GRADE
SMITH 800 A
ALLEN 1600 B
WARD 1250 B
JONES 2975 C
MARTIN 1250 B
2. CASE can work with predicates (like ,in etc) and searchable subqueries
DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.
Example:-
SQL>select e.ename,case when e.job in ('PRESODENT') then 'Boss'
when e.job in ('MANAGER') then 'Managers'
else 'General Employees'
end emp_category
from emp e
where rownum < 6;
ENAME EMP_CATEGORY
SMITH General Employees
ALLEN General Employees
WARD General Employees
JONES Managers
MARTIN General Employees
3. CASE can work as a PL/SQL construct
DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.
SQL> declare
2 grade char(1);
3 begin
4 grade := 'b';
5 case grade
6 when 'a' then dbms_output.put_line('excellent');
7 when 'b' then dbms_output.put_line('very good');
8 when 'c' then dbms_output.put_line('good');
9 when 'd' then dbms_output.put_line('fair');
10 when 'f' then dbms_output.put_line('poor');
11 else dbms_output.put_line('no such grade');
12 end case;
13 end;
14 /
PL/SQL procedure successfully completed.
CASE can even work as a parameter to a procedure call, while DECODE cannot.
SQL> var a varchar2(5);
SQL> exec :a := 'THREE';
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace procedure proc_test (i number)
2 as
3 begin
4 dbms_output.put_line('output = '||i);
5 end;
6 /
Procedure created.
SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);
output = 3
PL/SQL procedure successfully completed.
4. NULL handled in different way by both
select decode(null, null, 'NULL', 'NOT NULL') test from dual;
TEST
----
NULL
select case null when null then 'NULL'
else 'NOT NULL'
end test
from dual;
TEST
--------
NOT NULL
Below case query will work same as decode
select case when null is null then 'NULL'
else 'NOT NULL'
end test
from dual
TEST
--------
NULL
5. CASE expects datatype consistency, DECODE does not
Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.
select decode(2,1,1,'2','2','3') t from dual;
T
--------
2
select case 2 when 1 then 1
when '2' then '2'
else '3'
end t
from dual;
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
6. CASE is ANSI SQL-compliant and decode is the Oracle proprietary
7. The DECODE is shorter and easier to understand than CASE.
Example:-
select ename, decode (deptno, 10, 'Accounting',
20, 'Research',
30, 'Sales',
'Unknown') as department
from emp
where rownum < 6;
ENAME DEPARTMENT
---------- ----------
SMITH Research
ALLEN Sales
WARD Sales
JONES Research
MARTIN Sales
select ename, case deptno when 10 then 'Accounting'
when 20 then 'Research'
when 30 then 'Sales'
else 'Unknown'
end as department
from emp
where rownum < 6;
ENAME DEPARTMENT
---------- ----------
SMITH Research
ALLEN Sales
WARD Sales
JONES Research
MARTIN Sales
0 comments:
Post a Comment