CASE is an advanced function that the Oracle database supports. It is used to serve as an IF-THEN-ELSE statement. The CASE function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i.
Syntax:
CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END
Parameters: expression: It is an optional parameter which is used to specify the value that will be compared to the list of conditions. condition_1, condition_2, … condition_n: It is used to specify the conditions. All the conditions are of the same datatype. result_1, result_2, … result_n: It is used to specify the value to be returned if a condition is found to be true. All the results are of the same datatype.
Example:
SELECT name, CASE student WHEN 'PASS' THEN 'The student is PASS’ WHEN 'FAIL' THEN 'The student is FAIL’ ELSE 'The Result is on hold’ END FROM students; |
Explanation: Here, we are passing 2 conditions and 3 results. If student = ‘PASS’, the result will be ‘The student is PASS’. If the first condition does not satisfy, it will move to the next one, and if none of the conditions follow the else result will be the final result.
