Some differences between CASE and DECODE:
1. CASE is a statement while DECODE is a function.
2. CASE can work with logical operators other than "=".
3. DECODE performs an equality check only. CASE is capable of other logical comparisons such as <,>, BETWEEN, LIKE etc.
SELECT ename, empno,
DECODE( deptno, 10, 'Accounting'
, 20, 'Research'
, 30, 'Sales'
, 40, 'Operations'
, 'Unknown'
) Department
FROM emp
ORDER BY ename;
SELECT
(CASE
WHEN sal <1000 THEN 'Low'
WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END ) Salary
FROM emp
ORDER BY ename;
4. CASE can work with predicates and searchable queries.
5. DECODE works with expressions that are scalar values only, CASE can work with predicates and sub queries in searchable form.
SELECT CASE
-- predicates with IN
WHEN salary IN (9000, 10000) THEN '9K - 10K'
-- searchable subquery
WHEN EMP_NO IN (SELECT mgr_no FROM department ) THEN 'dept_mgr'
ELSE 'Unknown'
END category
FROM employee;
6. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.
7. DECODE can work as a function inside SQL only, CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL. In below example, if you replace CASE with DECODE then it gives error.
DECLARE
NUMBER :=20;
VARCHAR2(20);
BEGIN
dept_desc := CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END;
DBMS_OUTPUT.PUT_LINE(dept_desc);
END;
8. CASE can be used as parameter of a function/procedure, CASE can even work as a parameter to a procedure call, while DECODE cannot.
Exec MyProc(CASE:A when 'THREE' then 3 else 0 end);
above statement will not give error but below statement gives error.
Exec MyPorc(DECODE(:a, 'THREE', 3,0));
9. CASE expects datatype consistency, DECODE does not,
SELECT DECODE (200, 100, 100, '200', '200', '300') TEST from dual;
-- --------- output
TEST
--------
200
SELECT CASE 200 WHEN 100 THEN 100
WHEN '200' THEN '200'
ELSE '300'
END TEST
FROM dual;
---------------------------
Error on line 2 at position 14 WHEN '200' THEN '200'
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
10. CASE handles NULL differently
SELECT DECODE (NULL, NULL, 'This is NULL',
'THIS IS NOT NULL') TEST
from dual;
----------------------- output
TEST
---------
this is Null
SELECT CASE NULL WHEN NULL THEN 'THIS IS NULL'
ELSE 'THIS IS NOT NULL'
END TEST
FROM DUAL;
---------------------output;
TEST
--------
This is not Null
SELECT CASE WHEN NULL IS NULL THEN 'this is null'
ELSE 'this is not null'
END TEST
FROM dual;
-------------------output
TEST
--------
This is null
based on above comparison, you can identify which one will be the best use for your requirement.
