Job Search

Tuesday, February 20, 2018

The Difference Between DECODE and CASE


While the decode operator has been around since the earliest days of Oracle, the case operator was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful.

In terms of usage, case and decode both are used like to accomplish the "if then else" statements. 

Apart from the syntactical differences:
·       DECODE works with expressions which are scalar values, CASE can work with predicates and sub queries in searchable form.
·       In decode we can’t use for range (like 2000 to 4000 etc.) but case we can.
·       Oracle also boasts that CASE executes faster in the optimizer than does DECODE.
·       DECODE can be used Only inside SQL statement.
·       But CASE can be used any where even as a parameter of a function/procedure.
·       Both differ in the way NULL is handled in the expression.

The case function is a much better option because case is:
Ø  More flexible than DECODE.
Ø  Easier to read.
Ø  ANSI-compatible.

However, CASE is essentially a better implementation of DECODE so the reasons for using either are similar.

The decode function is considered the most powerful function in Oracle. Oracle 8i release introduced the CASE expression. The CASE expression can do all that DECODE does plus lot of other things including IF-THEN analysis, use of any comparison operator and checking multiple conditions, all in a SQL query itself.
Moreover, using the CASE function, multiple conditions provided in separate SQL queries can be combined into one, thus avoiding multiple statements on the same table.

Sr. No.
CASE
DECODE
1.
CASE can work with logical operators other than ‘=’ ,  CASE is capable of other logical comparisons such as < ,> ,BETWEEN , LIKE etc.
DECODE performs an equality check only.
2.
CASE can work with predicates and searchable sub queries.
DECODE works with expressions that are scalar values only.
3.
 CASE can work as a PL/SQL construct, CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.
DECODE can work as a function inside SQL only.
4.
CASE can be used as parameter of a function/procedure.
DECODE cannot.
5.
CASE expects data type consistency.
DECODE does not.
6.
CASE handles NULL differently.
In DECODE, Oracle consider two NULL to be equivalent.
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.
DECODE is a function.


Detailed examples:

1. CASE can work with logical operators other than ‘=’

SELECT e.first_name||' '||e.last_name Name,
       CASE
         WHEN e.salary < 10000 THEN
          'Grade I'
         WHEN (e.salary >= 10000 AND e.salary < 20000) THEN
          'Grade II'
         WHEN (e.salary >= 20000 AND e.salary < 30000) THEN
          'Grade III'
         ELSE
          'Grade IV'
       END sal_grade
  FROM employees e
 WHERE rownum < 5;

NAME
SAL_GRADE
Steven King
Grade III
Neena Kochhar
Grade II
Lex De Haan
Grade II
Alexander Hunold
Grade I

2. CASE can work with predicates and searchable sub queries

SELECT e.first_name || ' ' || e.last_name Name,
       CASE
       -- predicate with "in"
       -- set the category based on last_name list
         WHEN e.last_name IN ('King', 'Smith', 'Ward') THEN
          'Top Bosses'
       -- searchable subquery
       -- identify if this employee has a reportee
         WHEN EXISTS (SELECT 1
                 FROM employees emp1
                WHERE emp1.manager_id = e.employee_id) THEN
          'Managers'
         ELSE
          'General Employees'
       END emp_category
  FROM employees e
 WHERE rownum < 5;

NAME
EMP_CATEGORY
Steven King
Top Bosses
Neena Kochhar
Managers
Lex De Haan
Managers
Alexander Hunold
Managers

3. CASE can work as a PL/SQL construct

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'b';
  CASE grade
    WHEN 'a' THEN
      dbms_output.put_line('excellent');
    WHEN 'b' THEN
      dbms_output.put_line('very good');
    WHEN 'c' THEN
      dbms_output.put_line('good');
    WHEN 'd' THEN
      dbms_output.put_line('fair');
    WHEN 'f' THEN
      dbms_output.put_line('poor');
    ELSE
      dbms_output.put_line('no such grade');
  END CASE;
END;
/

Output
-------------------------------
very good

4. CASE can be used as parameter of a function/procedure

CREATE OR REPLACE PROCEDURE proc_test(i NUMBER) AS
BEGIN
  dbms_output.put_line('output = ' || i);
END;
/

BEGIN
proc_test(decode('THREE', 'THREE', 3, 0));
END;

Error
-------------------------------
ORA-06550: line 2, column 11:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

BEGIN
proc_test(CASE 'THREE' WHEN 'THREE' THEN 3 ELSE 0
END);
END;

Output
-------------------------------
output = 3

5. CASE expects data type consistency, DECODE does not
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
  FROM dual;

Error
-------------------------------
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

6. CASE handles NULL differently

SELECT DECODE(NULL, NULL, 'NULL', 'NOT NULL') NULL_TEST FROM DUAL;

NULL_TEST
NULL

SELECT CASE NULL
    WHEN NULL THEN 'NULL'
    ELSE 'NOT NULL'
END NULL_TEST FROM DUAL;

NULL_TEST
NOT NULL

SELECT CASE
    WHEN NULL IS NULL THEN 'NULL'
    ELSE 'NOT NULL'
END NULL_TEST
FROM DUAL;

NULL_TEST
NULL

7. The difference in readability
In very simple situations, DECODE is shorter and easier to understand than CASE.

Complicated logical comparisons in DECODE, even if technically achievable, are a recipe for messy, bug-prone code. When the same can be done more cleanly with CASE, go for CASE.

SELECT e.first_name || ' ' || e.last_name NAME,
       decode(e.department_id,
              80,
              'Accounting',
              90,
              'Research',
              100,
              'Sales',
              'Unknown') AS department
  FROM employees e
 WHERE rownum < 5;

NAME
DEPARTMENT
Steven King
Research
Neena Kochhar
Research
Lex De Haan
Research
Alexander Hunold
Unknown

SELECT e.first_name || ' ' || e.last_name NAME,
       CASE e.department_id
         WHEN 80 THEN
          'Accounting'
         WHEN 90 THEN
          'Research'
         WHEN 100 THEN
          'Sales'
         ELSE
          'Unknown'
       END AS department
  FROM employees e
 WHERE rownum < 5;

NAME
DEPARTMENT
Steven King
Research
Neena Kochhar
Research
Lex De Haan
Research
Alexander Hunold
Unknown


I hope you all have enjoyed reading this article. Comments are welcome....

No comments:

Post a Comment