________________________________________
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:
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:
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:
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:
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:
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:
SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
2 CASE WHEN HIREDATE
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:
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:
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:
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:
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:
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>
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:
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:
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:
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.
No comments:
Post a Comment