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.
