CASE expression in Oracle

What is CASE expression?

You are very well familiar with if..then…else clause, isn’t it? But what if we want to use this if condition in single query of Oracle? No worries. We can do this with the help of CASE expression.

CASE expressions allows 255 arguments in single query. It means you can write 255 if…then…else conditions. Amazing, isn’t it?

Let us find more…

There are two types of CASE expressions

  1. Simple CASE Expression
  2. Searched CASE Expression

Simple CASE Expression

Simple CASE expression searches for first WHEN…THEN pair where comparison expression is validated. If this validation results in TRUE then value in THEN clause is returned otherwise value in ELSE clause is returned. If ELSE clause is not written then NULL value is returned. (So many if and then…)

What should be the data types in WHEN and THEN?

For Simple CASE expression, value in WHEN clause and in THEN clause must have same data types or numeric data types. If all the data types are numeric then Oracle choose the argument with highest numeric precedence and implicitly converts the remaining arguments to that data type, and returns that data type.

Syntax

SELECT...CASE expression1 WHEN comparision_value1 THEN return_value1
 WHEN comparison_value2 THEN return_value2
 ELSE return_value3 END
 FROM table_name;

Example

SELECT employee_number, CASE dept_no WHEN 10 THEN 'IT'
 WHEN 20 THEN 'ADMIN'
 ELSE 'General' END
 FROM employee;

Searched CASE Expression

Searched CASE expression searches from left to right untill it finds true condition and returns the value mentioned in THEN clause. If condition is not found to be true it returns value mentioned in ELSE clause. If ELSE clause is not written then NULL is returned.

Syntax

SELECT...CASE expression1 WHEN comparision_value1 THEN return_value1
 WHEN comparison_value2 THEN return_value2
 ELSE return_value3 END
 FROM table_name;

Example

SELECT employee_number, CASE WHEN age > 58 THEN 'Retired Employee'
 ELSE 'Working Employee' END
 FROM employee;

CASE expressions are used extensively in SQL queries. Many business requirements can be satisfied in one go using this wonderful SQL clause.

If you found this post helpful or if you want to shed some more light on it, please put your comments below.

Also you can Subscribe here to stay updated on latest posts.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.