1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Case and decode : two powerful constructs of sql

Discussion in 'SQL PL/SQL' started by rajavu, Nov 1, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    CASE and DECODE are the two widely used constructs in the SQL . And both have the functionality of an IF-THEN-ELSE statement to return some specified value meeting some criteria.Even though they are used interchangeably there are some differences between them.

    This article tries to show list the advantage of CASE over DECODE and also explain how to convert DECODE to CASE and vice versa.

    CASE was introduced in Oracle 8.1.6 as a replacement for the DECODE . Anyway it is much better option than DECODE as it is ,

    1. More Flexible than DECODE

    2. More easier to read

    3. ANSI Compatible

    4. compatible in PL/SQL Context

    SIMPLE CASE

    Generally CASE has two syntaxes as below

    a. Expression Syntax

    Code (Text):

    CASE  [ expression ]
      WHEN Value_1 THEN result_1
      WHEN Value_2 THEN result_2
      ...
      WHEN Value_n THEN result_n
      [ELSE else_result]
    END
     
    Here CASE checks the value of Expression and returns the result each time for each record as specified. Here is one such example to list the new salaries for all employees

    Code (Text):

    SQL> SELECT EMPNO,JOB , SAL ,
      2              CASE JOB WHEN 'ANALYST' THEN SAL*1.2
      3                  WHEN 'MANAGER' THEN SAL*1.4
      4              ELSE SAL END  NEWSAL
      5  FROM EMP;

         EMPNO JOB              SAL     NEWSAL
    ---------- --------- ---------- ----------
          7369 CLERK            800        800
          7499 SALESMAN        1600       1600
          7521 SALESMAN        1250       1250
          7566 MANAGER         2975       4165
          7654 SALESMAN        1250       1250
          7698 MANAGER         2850       3990
          7782 MANAGER         2450       3430
          7788 ANALYST         3000       3600
          7839 PRESIDENT       5000       5000
          7844 SALESMAN        1500       1500
          7876 CLERK           1100       1100
          7900 CLERK            950        950
          7902 ANALYST         3000       3600
          7934 CLERK           1300       1300

    14 rows selected.

    SQL>
     
    The Equivalent DECODE syntax will be

    Code (Text):

    SQL> SELECT EMPNO,JOB , SAL ,
      2              DECODE (JOB,'ANALYST', SAL*1.2 ,
      3                          'MANAGER', SAL*1.4,
      4                          SAL )  NEWSAL
      5  FROM EMP;

         EMPNO JOB              SAL     NEWSAL
    ---------- --------- ---------- ----------
          7369 CLERK            800        800
          7499 SALESMAN        1600       1600
          7521 SALESMAN        1250       1250
          7566 MANAGER         2975       4165
          7654 SALESMAN        1250       1250
          7698 MANAGER         2850       3990
          7782 MANAGER         2450       3430
          7788 ANALYST         3000       3600
          7839 PRESIDENT       5000       5000
          7844 SALESMAN        1500       1500
          7876 CLERK           1100       1100
          7900 CLERK            950        950
          7902 ANALYST         3000       3600
          7934 CLERK           1300       1300

    14 rows selected.

    SQL>
     
    b. Conditional syntax

    Code (Text):

    CASE  
      WHEN Condition_1 THEN result_1
      WHEN Condition_2 THEN result_2
      ...
      WHEN Condition_n THEN result_n
      [ELSE else_result]
    END
     
    Here CASE tries to return the values on meeting some conditions rather than checking for the expressions . Here is such an example with the same functionality as above .

    Code (Text):

    SQL> SELECT EMPNO, JOB , SAL ,
      2              CASE  WHEN JOB='ANALYST' THEN SAL*1.2
      3                    WHEN JOB='MANAGER' THEN SAL*1.4
      4              ELSE SAL  END  NEWSAL
      5  FROM EMP;

         EMPNO JOB              SAL     NEWSAL
    ---------- --------- ---------- ----------
          7369 CLERK            800        800
          7499 SALESMAN        1600       1600
          7521 SALESMAN        1250       1250
          7566 MANAGER         2975       4165
          7654 SALESMAN        1250       1250
          7698 MANAGER         2850       3990
          7782 MANAGER         2450       3430
          7788 ANALYST         3000       3600
          7839 PRESIDENT       5000       5000
          7844 SALESMAN        1500       1500
          7876 CLERK           1100       1100
          7900 CLERK            950        950
          7902 ANALYST         3000       3600
          7934 CLERK           1300       1300

    14 rows selected.

    SQL>
     
    Even DECODE can be a replacement for such condition scenario ,but it cannot have as flexibility as CASE can have . Here is such scenario to display the hiked the salary for only for those employees who were hired before 01-JAN-1982.

    Code (Text):

    SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
      2              CASE  WHEN HIREDATE <TO_DATE('01/01/1982','DD/MM/YYYY')
      3                    THEN SAL*1.6
      4                    ELSE SAL
      5             END  NEWSAL
      6  FROM EMP;

         EMPNO HIREDATE  JOB              SAL     NEWSAL
    ---------- --------- --------- ---------- ----------
          7369 17-DEC-80 CLERK            800       1280
          7499 20-FEB-81 SALESMAN        1600       2560
          7521 22-FEB-81 SALESMAN        1250       2000
          7566 02-APR-81 MANAGER         2975       4760
          7654 28-SEP-81 SALESMAN        1250       2000
          7698 01-MAY-81 MANAGER         2850       4560
          7782 09-JUN-81 MANAGER         2450       3920
          7788 09-DEC-82 ANALYST         3000       3000
          7839 17-NOV-81 PRESIDENT       5000       8000
          7844 08-SEP-81 SALESMAN        1500       2400
          7876 12-JAN-83 CLERK           1100       1100
          7900 03-DEC-81 CLERK            950       1520
          7902 03-DEC-81 ANALYST         3000       4800
          7934 23-JAN-82 CLERK           1300       1300

    14 rows selected.

    SQL>
     
    The Equivalent code for DECODE will be ,

    Code (Text):

    SQL> SELECT EMPNO ,HIREDATE ,JOB , SAL ,
      2              DECODE (LEAST ( HIREDATE , TO_DATE('01/01/1982','DD/MM/YYYY')),
      3                              HIREDATE ,SAL*1.6,
      4                              SAL)   NEWSAL
      5  FROM EMP;

         EMPNO HIREDATE  JOB              SAL     NEWSAL
    ---------- --------- --------- ---------- ----------
          7369 17-DEC-80 CLERK            800       1280
          7499 20-FEB-81 SALESMAN        1600       2560
          7521 22-FEB-81 SALESMAN        1250       2000
          7566 02-APR-81 MANAGER         2975       4760
          7654 28-SEP-81 SALESMAN        1250       2000
          7698 01-MAY-81 MANAGER         2850       4560
          7782 09-JUN-81 MANAGER         2450       3920
          7788 09-DEC-82 ANALYST         3000       3000
          7839 17-NOV-81 PRESIDENT       5000       8000
          7844 08-SEP-81 SALESMAN        1500       2400
          7876 12-JAN-83 CLERK           1100       1100
          7900 03-DEC-81 CLERK            950       1520
          7902 03-DEC-81 ANALYST         3000       4800
          7934 23-JAN-82 CLERK           1300       1300

    14 rows selected.

    SQL>
     
    But here CASE query is more flexible , readable and elegant.

    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 (Text):

    SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
      2              CASE  WHEN HIREDATE < TO_DATE('01/01/1982','DD/MM/YYYY')
      3                    THEN  CASE  WHEN JOB='ANALYST' THEN SAL*1.2
      4                                WHEN JOB='MANAGER' THEN SAL*1.4
      5                                ELSE SAL END
      6                    ELSE CASE   WHEN JOB='ANALYST' THEN SAL*1.6
      7                                ELSE SAL END     END  NEWSAL
      8  FROM EMP;

         EMPNO HIREDATE  JOB              SAL     NEWSAL
    ---------- --------- --------- ---------- ----------
          7369 17-DEC-80 CLERK            800        800
          7499 20-FEB-81 SALESMAN        1600       1600
          7521 22-FEB-81 SALESMAN        1250       1250
          7566 02-APR-81 MANAGER         2975       4165
          7654 28-SEP-81 SALESMAN        1250       1250
          7698 01-MAY-81 MANAGER         2850       3990
          7782 09-JUN-81 MANAGER         2450       3430
          7788 09-DEC-82 ANALYST         3000       4800
          7839 17-NOV-81 PRESIDENT       5000       5000
          7844 08-SEP-81 SALESMAN        1500       1500
          7876 12-JAN-83 CLERK           1100       1100
          7900 03-DEC-81 CLERK            950        950
          7902 03-DEC-81 ANALYST         3000       3600
          7934 23-JAN-82 CLERK           1300       1300

    14 rows selected.

    SQL>
     
    It equivalent decode statement will be

    Code (Text):

    SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
      2              DECODE (LEAST ( HIREDATE , TO_DATE('01/01/1982','DD/MM/YYYY')),
      3                               HIREDATE ,
      4                                DECODE (JOB,'ANALYST', SAL*1.2 ,
      5                                            'MANAGER', SAL*1.4,
      6                                                    SAL ),
      7                               DECODE (JOB, 'ANALYST', SAL*1.6,
      8                                                    SAL ))   NEWSAL
      9  FROM EMP;

         EMPNO HIREDATE  JOB              SAL     NEWSAL
    ---------- --------- --------- ---------- ----------
          7369 17-DEC-80 CLERK            800        800
          7499 20-FEB-81 SALESMAN        1600       1600
          7521 22-FEB-81 SALESMAN        1250       1250
          7566 02-APR-81 MANAGER         2975       4165
          7654 28-SEP-81 SALESMAN        1250       1250
          7698 01-MAY-81 MANAGER         2850       3990
          7782 09-JUN-81 MANAGER         2450       3430
          7788 09-DEC-82 ANALYST         3000       4800
          7839 17-NOV-81 PRESIDENT       5000       5000
          7844 08-SEP-81 SALESMAN        1500       1500
          7876 12-JAN-83 CLERK           1100       1100
          7900 03-DEC-81 CLERK            950        950
          7902 03-DEC-81 ANALYST         3000       3600
          7934 23-JAN-82 CLERK           1300       1300

    14 rows selected.

    SQL>
     
    One major advantage of CASE over DECODE

    CASE works good in bot SQL and PL/SQL Context while DECODE is supported only in SQL Context. Below example explains this scenario.

    Code (Text):

    SQL> CREATE OR REPLACE FUNCTION GIMME_VAL ( N NUMBER)
      2  RETURN NUMBER AS
      3  BEGIN
      4  RETURN N*N ;
      5  END;
      6  /

    Function created.

    SQL> variable x number
    SQL> begin
      2  :x :=10;
      3  end;
      4  /

    PL/SQL procedure successfully completed.

    SQL> select Gimme_val(decode(:x,0,1,:x)) from dual;

    GIMME_VAL(DECODE(:X,0,1,:X))
    ----------------------------
                             100

    SQL> select Gimme_val(CASE :x WHEN 0 THEN 1 ELSE :x End ) from dual;

    GIMME_VAL(CASE:XWHEN0THEN1ELSE:XEND)
    ------------------------------------
                                     100

    SQL>
     
    Both are working well in SQL context . And to test it in PL/SQL , lets create two procedures ; one with CASE and another with DECODE.

    Code (Text):

    SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_1 AS
      2  X NUMBER :=10 ;
      3  Z NUMBER ;
      4  BEGIN
      5  Z := GIMME_VAL(CASE X WHEN 0 THEN 1 ELSE X END );
      6  DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
      7  END;
      8  /

    Procedure created.

    SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_2 AS
      2  X NUMBER :=10;
      3  Z NUMBER;
      4  BEGIN
      5  Z := GIMME_VAL(DECODE(X,0,1,X));
      6  DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
      7  END;
      8  /

    Warning: Procedure created with compilation errors.

    SQL> show error
    Errors for PROCEDURE GETME_VAL_2:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/1      PL/SQL: Statement ignored
    5/16     PLS-00204: function or pseudo-column 'DECODE' may be used inside
             a SQL statement only

    SQL>
     
    Yup !! We got one compilation error while creating a procedure to use the DECODE inside the PL/SQL context . ie "function or pseudo-column 'DECODE' may be used inside a SQL statement only"

    Anyway there is alternative to overcome this error. We need to incorporate the DECODE in an Select statement as below.

    Code (Text):

    SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_2 AS
      2  X NUMBER :=10;
      3  Z NUMBER;
      4  BEGIN
      5      SELECT GIMME_VAL(DECODE(X,0,1,X))
      6      INTO Z
      7      FROM DUAL;
      8  DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
      9  END;
     10  /

    Procedure created.

    SQL>
     
    And now both the procedure will work fine .

    Code (Text):

    SQL> Exec getme_val_1 ;
    GIMME_VAL = 100

    PL/SQL procedure successfully completed.

    SQL> Exec getme_val_2 ;
    GIMME_VAL = 100

    PL/SQL procedure successfully completed.

    SQL>
     
    An interesting scenario

    Its really interesting !!! Understand the scenario without any explanation .

    Code (Text):

    SQL> SELECT DECODE (NULL,NULL,'NULL','NOTNULL') NICE
      2  FROM DUAL;

    NICE
    ----
    NULL

    SQL> SELECT CASE NULL
      2         WHEN NULL THEN 'NULL'
      3         ELSE 'NOTNULL' END NICE
      4  FROM DUAL;

    NICE
    -------
    NOTNULL

    SQL> SELECT CASE
      2         WHEN NULL IS NULL THEN 'NULL'
      3         ELSE 'NOTNULL' END NICE
      4  FROM DUAL;

    NICE
    -------
    NULL

    SQL>
     
    Conclusion

    Though both CASE and DECODE are used interchangeably , CASE is definitely proved to be better and elegant option over DECODE as it is more flexible , readable and ANSI Compatible . And moreover CASE is perffered in PL/SQL statements as DECODE is only supported in the SQL statements.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is One important thing to remember while using Decode or CASE. The results arising out of the CASE or DECODE should be of the same data type. Otherwise queries will give the errors.

    Lets check some examples.

    Code (Text):

    SQL> SELECT EMPNO , JOB ,
      2         DECODE( JOB,'MANAGER',SAL,'NOSAL' ) SAL
      3  FROM EMP;
           DECODE( JOB,'MANAGER',SAL,'NOSAL' ) SAL
                                     *
    ERROR at line 2:
    ORA-01722: invalid number


    SQL> SELECT EMPNO , JOB ,
      2         CASE JOB WHEN 'MANAGER' THEN SAL ELSE 'NOSAL' END
      3   FROM EMP;
           CASE JOB WHEN 'MANAGER' THEN SAL ELSE 'NOSAL' END
                                                 *
    ERROR at line 2:
    ORA-00932: inconsistent datatypes: expected NUMBER got CHAR


    SQL>
     
    Here Query tries to display Salary for managers and 'NOSAL' for others. both the queries failed with different errors , the basic reason being the different datatypes for the results.

    Now lets check for another couple of interesting queries.

    Code (Text):

    SQL> SELECT EMPNO , JOB ,
      2          CASE JOB WHEN 'CLERK' THEN 'NOSAL' ELSE SAL END SAL
      3  FROM EMP;
            CASE JOB WHEN 'CLERK' THEN 'NOSAL' ELSE SAL END SAL
                                                    *
    ERROR at line 2:
    ORA-00932: inconsistent datatypes: expected CHAR got NUMBER


    SQL> SELECT EMPNO , JOB ,
      2         DECODE( JOB,'CLERK','NOSAL',SAL ) SAL
      3  FROM EMP;

         EMPNO JOB       SAL
    ---------- --------- ----------------------------------------
          7369 CLERK     NOSAL
          7499 SALESMAN  1600
          7521 SALESMAN  1250
          7566 MANAGER   2975
          7654 SALESMAN  1250
          7698 MANAGER   2850
          7782 MANAGER   2450
          7788 ANALYST   3000
          7839 PRESIDENT 5000
          7844 SALESMAN  1500
          7876 CLERK     NOSAL
          7900 CLERK     NOSAL
          7902 ANALYST   3000
          7934 CLERK     NOSAL

    14 rows selected.

    SQL>
     
    Surprised ??? CASE doesn't work and DECODE Works !!! Actually DECODE is trying to cast/convert the result_2 datatype to result_1 datatype ( number to varchar2) and it works. But CASE never tries for conversion. And this is the same reason DECODE fetched error in our previous example where it tried to convert varchar2 to Number , but failed. So it is always recommended to have the same datatypes for all the results while using CASE or DECOCDE. (obviously the only exception could be NULL )

    .
     
  3. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Good work Rajavu. I was trying some complex examples with case decode. As soon as I will stand some I will let you know.
    Regards,
     
  4. ShaheerBadar

    ShaheerBadar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    thanks you
     
  5. abhishek maheshwari

    abhishek maheshwari Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    bangalore, karnataka
    Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional statement.

    Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

    Example with DECODE function
    Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function.
    Code (SQL):
    SELECT
    decode (
    region,
    ‘N’,’North’,
    ‘S’,’South’,
    ‘E’,’East’,
    ‘W’,’West’,
    UNKNOWN
    )
    FROM
    customer;
    Example with CASE statement
    Code (SQL):
    SELECT
    CASE
    region
    WHEN ‘N’ THEN ’North’
    WHEN ‘S’ THEN ’South’
    WHEN ‘E’ THEN ’East’,
    WHEN ‘W’ THEN ’West’
    ELSEUNKNOWN
    END
    FROM
    customer;
    Difference between DECODE and CASE:
    Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Following is the list of differences -
    1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
    2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
    3. CASE expects datatype consistency, DECODE does not.
    4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
    5. CASE executes faster in the optimizer than does DECODE.
    6. CASE is a statement while DECODE is a function.

    Source:- http://tech.queryhome.com/32211/what-the-difference-between-decode-and-case-statement-oracle