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   empemp1

                                WHERE   emp1.mgr = e.empno)

                     THEN

                        'Managers'

                     ELSE

                        'General Employees'

                  END

                     emp_category

  FROM   empe

 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

            WHENHIREDATE < 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;

--------------------------------------------------------------------------------------------------------------------------

NVL

The NVL function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.

• NVL ( expr1 , expr2 ): If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.y is alternate value when n is NULL

  NVL2

• NVL2 ( expr1 , expr2 , expr3 ): If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2
  
  
  Syntax
  The NVL2 function takes three arguments. The first argument (could also be an expression) is evaluated for NULL or NOT NULL. When this argument evaluates to NOT NULL, the second expression is returned. When the first argument evaluates to NULL then last (third) expression is returned.
  
  It works like this pseudo code:

  IF argument1 IS NOT NULL    THEN       argument2;    ELSE       argument3;    END IF;