Total Pageviews

February 4, 2015

2/04/2015 11:01:00 PM
Oracle SQL Decode statements


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;

 
Related Posts Plugin for WordPress, Blogger...