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

Example

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

Example

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.

Leave a Reply

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