Difference between Case and Decode

Difference between Case and Decode

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

2.CASE can work with predicates and searchable sub queries

SQL> SELECT   e.ename, CASE

                     -- predicate with "in"

                     -- set the category based on ename list

                  WHEN e.ename IN ('KING', 'SMITH', 'WARD')

                     THEN

                        'Top Bosses'

                     -- searchable subquery

                     -- identify if this emp has a reportee

                  WHEN EXISTS (SELECT   1

                                 FROM   emp emp1

                                WHERE   emp1.mgr = e.empno)

                     THEN

                        'Managers'

                     ELSE

                        'General Employees'

                  END

                     emp_category

  FROM   emp e

 WHERE   ROWNUM < 5;

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.

4. CASE expects datatype consistency, DECODE does not

5 The difference in readability

In very simple situations, DECODE is shorter and easier to understand than CASE.

6) NESTED CASE

What we discussed till now is about the simple CASE . But it can be nested also. Lets make it clear with some examples . Here is such an example to hike the salaries only for Analysts and Managers joined before 01-JAN-1982 and Analysts joined on or after the same date.

Code :

SELECT   EMPNO,

         HIREDATE,

         JOB,

         SAL,

         CASE

            WHEN HIREDATE < TO_DATE ('01/01/1982', 'DD/MM/YYYY')

            THEN

               CASE

                  WHEN JOB = 'ANALYST' THEN SAL * 1.2

                  WHEN JOB = 'MANAGER' THEN SAL * 1.4

                  ELSE SAL

               END

            ELSE

               CASE WHEN JOB = 'ANALYST' THEN SAL * 1.6 ELSE SAL END

         END

            NEWSAL

  FROM   EMP;

Share on Google Plus

About sekhar

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.