Hi,This is Halim,working as a Sr. Oracle DBA and PL/SQL developer in Atlanta, USA. I very much like to play with SQL, PL/SQL and scripting languages along with my DBA job. I got a recognition for PL/SQL. Please see here at 16th position(http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html). I am maintaining this blog for helping myself while in work and sharing my knowledge with others. If you have any question. you can reach me at infohalimdba@gmail.com Thanks.

Thursday, September 17, 2009

USE OF CASE AND DECODE

CASE AND DECODE : Two powerful constructs of SQL ________________________________________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 SyntaxCode:CASE [ expression ] WHEN Value_1 THEN result_1 WHEN Value_2 THEN result_2 ... WHEN Value_n THEN result_n [ELSE else_result]ENDHere 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;

SQL>b. Conditional syntaxCode:CASE WHEN Condition_1 THEN result_1 WHEN Condition_2 THEN result_2 ... WHEN Condition_n THEN result_n [ELSE else_result]ENDHere 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;

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 3 THEN SAL*1.6 4 ELSE SAL 5 END NEWSAL 6 FROM EMP;

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;

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 /

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.