Oracle PLSQL: CASE Expression

CASE in sql is similar to IF .. THEN .. ELSE logic in PLSQL.

 

The Syntax is:

 

CASE [Expression]

     WHEN   Condition1   THEN  result1

     WHEN   Condition2   THEN  result2

     ....

     WHEN   Conditionn   THEN  resultn

     ....

     ELSE result

END

     

Case statement starts with CASE keyword and contains atleast one WHEN & THEN cluase.

 

Expression in the syntax above is optional, it is the value that would be compared against the conditions in WHEN clause (i.e., condition1, condition2, condition).

 

All conditions must be of the same datatype. Conditions are evaluated in the order in which they are written in the CASE statement.

 

Also all the results in the THEN clause must be of same datatype. This is the valuse returned once the condition is satisfied.

 

If no condition is found true, then CASE would return result from ELSE clause.

 

If ELSE is omitted and no condition is true then CASE would return NULL.

 

Upto 255 comparisons are allowed in a case statement.

 

Case is available in the below versions of Oracle.

 

Oracle 9i

Oracle 10g

Oracle 11g

Oracle 12c

 

Example:

 

DROP TABLE students;

 

CREATE TABLE students

(

   student_id            NUMBER,

   student_name          VARCHAR2(200),

   percentage            NUMBER

);

 

INSERT INTO students (student_id,student_name, percentage) VALUES (1,'John', 100);

INSERT INTO students (student_id,student_name, percentage) VALUES (2,'Henry', 65);

INSERT INTO students (student_id,student_name, percentage) VALUES (3,'William', 53);

INSERT INTO students (student_id,student_name, percentage) VALUES (4,'Gosh', 43);

INSERT INTO students (student_id,student_name, percentage) VALUES (5,'Abhay', 39);

COMMIT;

 

SELECT CASE

          WHEN percentage > 70 THEN'DISTINCTION'

          WHEN percentage > 60 THEN'FIRST CLASS'

          WHEN percentage > 50 THEN'SECOND CLASS'

          WHEN percentage > 40 THEN'THIRD CLASS'

          ELSE 'FAIL'

       END,

       student_name

  FROM students;

 

Above is a simple example for classifying students based on the percentage of marks received. Students with 70% or more are classified as DISTINCTION, BETWEEN 60 AND 70 as ‘FIRST CLASS’, BETWEEN 50 AND 60 as ‘SECOND CLASS’, BETWEEN 40 AND 50 as ‘THIRD CLASS’ and the rest of them as FAIL.

SELECT CASE 1

        WHEN 1 THEN 'ONE'

        WHEN 2 THEN 'TWO'

        WHEN 3 THEN 'THREE'

        ELSE 'NONE' END

  FROM DUAL;

 

Above is a simple example for using CASE statement with expression evaluation.

Oracle/PLSQL: Decode Function

Decode has the functionality a IF - THEN - ELSE statement.

The syntax for decode function is :

   DECODE(expression, search, result [,search,result]...[,default])


Expression is the values to be compared.
Search is the value compared against expression.
Result is the value returned, if expression equal to search.
Default is an optional field. If no matches are found default would be returned. If default is omitted the decode would return a NULL value when there is no match.

The max number of arguments the Decode function can have is 255
(When more than 255 arguments are passed we always get a ORA-00939: too many arguments for function exception).

An example:

SELECT DECODE(department_id, 100, 'Finance',
                                                        200, 'Audit',
                                                        300, 'IT',
                                                        400, 'Engineering',
                                                        500, 'Admin',
                                                                'Management') result
FROM departments;

The above DECODE function can be written using IF - THEN - ELSE

IF department_id = 100 THEN
result := 'Finance';
ELSIF department_id = 200 THEN
result := 'Audit';
ELSIF department_id = 300 THEN
result := 'IT';
ELSIF department_id = 400 THEN
result := 'Engineering';
ELSIF department_id = 500 THEN
result := 'Admin';
ELSE
result := 'Management';
END IF;


It is always a good practice to keep the data-type of the result in the Decode function to be consistent.

SELECT DECODE(abc, 'TRUE', 'T',
                                          'FALSE', 'F',
                                          NULL, 1,
                                                      0) Result FROM DUAL;

In the above statement depending on the value of abc the statement would return 'T', 'F', 1, 0.
There is no syntax error here but its a bad practice to have more then one data-type in the return value.
There is a possibility of a run time error(ORA: 06512 invalid number exception or value error exception) while using inconsistent data-types in the return clause

SELECT DECODE(abc, 'TRUE', 'T',
                                          'FALSE', 'F',
                                          NULL, '1',
                                                      '0') Result FROM DUAL;

Always a good practice to use similar data-types as in the above statement 'T','F','1','0'.