DECODE and CASE both provides IF-THEN-ELSE functionality in Oracle SQL. Decode Function and Case Statement is used to transform data values at retrieval time. Before Oracle 8.1 version, only DECODE function was there for providing IF-THEN-ELSE functionality and this can compare only discrete values (Not in range). In Oracle version 8.1.6, Oracle introduced the CASE Statement, which allowed the use of operators like <,> and BETWEEN, IN etc. Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.DECODE Function:
- It is Extension to ANSI SQL.
- It is like IF…THEN. . ELSE function.
- It compares VALUE to IF value , one by one.
- If equal match found, return corresponding THEN value.
- If match is not found, the ELSE value is return, if coded.
- If ELSE is not coded and a match is not found, NULL is returned.
- VALUE, IF , THEN and ELSE can be expression (e.g. : SYSDATE – BIRTHDATE ) or function ( e.g. SIGN(salary – avg_salary) ).
- In DECODE, Oracle consider two NULL to be equivalent.
- Maximum number of item, including VALUE, IF, THEN and ELSE is 255.
DECODE( VALUE, IF_1 , THEN_1 [,IF_2 ,THEN_2] [,IF_N ,THEN_N] [,ELSE] )
Above syntax can be understand like below:
DECODE( VALUE, search_1 ,result_1 [,search_2 ,result_2] [,search_N ,result_N] [,default] )
Example:
SELECT supplier_name, DECODE(supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result FROM suppliers;
Above DECODE statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN result := 'IBM'; ELSIF supplier_id = 10001 THEN result := 'Microsoft'; ELSIF supplier_id = 10002 THEN result := 'Hewlett Packard'; ELSE result := 'Gateway'; END IF;
CASE Statement:
CASE expression WHEN this1 THEN that1 WHEN this2 THEN that2 [ ELSE that] END
Simple case expression use for an equal condition on the given value or expression.The first WHEN value which is match with given value, return corresponding THEN value. If none of the WHEN value match with given value/expression , the ELSE values is returned. If the ELSE is not coded, NULL is returned.
Example:
SELECT ename, empno, (CASE deptno WHEN 10 THEN 'Accounting' WHEN 20 THEN 'Research' WHEN 30 THEN 'Sales' WHEN 40 THEN 'Operations' ELSE 'Unknown' END) department FROM emp ORDER BY ename;
B) Searched CASE statement:
CASE WHEN condition_1 THEN return_value1 WHEN condition_2 THEN return_value2 [ ELSE return_value] END
The searched CASE allow multiple comparison expression (<, > , <=, >=, BETWEEN, LIKE, IN, IS NULL, etc.). The first TRUE expression cause oracle to return the corresponding THEN value. If none of the WHEN values match the given expression, the ELSE value is returned. If the ELSE is not coded, NULL is returned.
Example:
SELECT CASE WHEN salary BETWEEN 6000 and 8000 THEN '6K-8K' WHEN salary IN (9000,10000) THEN '9K-10K' WHEN EXISTS (SELECT NULL FROM avg_sal WHERE avg_sal = salary) THEN 'EXISTS' WHEN TO_CHAR(salary) LIKE '3%' THEN 'Like 3' WHEN SALARY IS NULL THEN 'Null' WHEN EMP_NO IN (SELECT mgr_no FROM department) THEN 'Dept_Mgr' ELSE 'Unknown' END Salary_Range FROM employee, avg_sal;
Note: CASE is limited to 128 WHEN/THEN pairs(255 total values). this limitation can be overcome by nesting cASE within CASE.
Difference Between DECODE and CASE:
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;
2) CASE can work with predicates and searchable subqueries:
SELECT CASE -- predicate 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 ;
3) CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.
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;
4) CASE can be used as parameter of a function/procedure.
exec proc_test(case :a when 'THREE' then 3 else 0 end);
Above statement will not give error but below statement gives error.
exec proc_test(decode(:a,'THREE',3,0));
5) 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
6) 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
7) CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
8) CASE executes faster in the Optimizer than does DECODE.
9) CASE is a statement while DECODE is a function.
