Can a CASE Statement Have Multiple Conditions?

Executing multiple conditions at the same time in the case statement can save the user a lot of time. To do this, the user has to make use of the WHEN clauses to add a condition in the CASE statement. In the case of multiple conditions, multiple WHEN clauses can be added.

This post will guide you on executing multiple conditions inside the CASE statement in the Oracle database.

Can a CASE Statement Have Multiple Conditions?

Yes, the user can execute multiple conditions inside the “CASE” statement with the help of the “WHEN” clauses. Additionally, the user has the option to include an “ELSE” clause along with the “WHEN” clause to add the default result.

Let’s see the syntax of having multiple conditions in a “CASE” statement:

CASE
WHEN [condition1] THEN [result1]
WHEN [condition2] THEN [result2]
...
WHEN [conditionN] THEN [resultN]
ELSE [default-RESULT]
END

In the above syntax, it is clear that the multiple conditions have been used with their particular output.

Let’s move to an example of a “CASE” statement having multiple conditions with a “WHEN” clause:

SELECT WAREHOUSE_ID,
CASE
WHEN LOCATION_ID <= 8 THEN '-> Area 1'
WHEN LOCATION_ID <= 16 THEN '-> Area 2'
ELSE '-> Not Recognize'
END AS Area
FROM WAREHOUSES;

In the above example, conditions have been applied on the “LOCATION_ID” column of the “WAREHOUSES” table.

Output

The output depicts that the conditions have been applied.

Multiple conditions in a CASE statement can be applied to single or multiple columns. Let’s see some examples for practical demonstration.

Example 1: Applying CASE Statement With Multiple Conditions on a Single Column

Let’s see an example of applying a CASE statement having multiple conditions on a single column:

SELECT COUNTRY_NAME,
CASE
WHEN REGION_ID = 1 THEN '-> Europe'
WHEN REGION_ID = 2 THEN '-> Americas'
WHEN REGION_ID = 3 THEN '-> Asia'
WHEN REGION_ID = 4 THEN '-> Middle East and Africa'
END AS REGION
FROM COUNTRIES;

In the above example, the conditions have been applied on the “REGION_ID” column of the “COUNTRIES” table.

Output

The output showed the result according to the conditions.

Example 2: Applying CASE Statement With Multiple Conditions on Multiple Columns

Let’s see an example of applying a CASE statement having multiple conditions on multiple columns:

SELECT ORDER_ID, STATUS,
CASE
WHEN STATUS = 'Shipped' THEN '-> Order Shipped <-'
WHEN STATUS = 'Canceled' THEN '-> Order Canceled <-'
WHEN ORDER_ID <= 20 THEN '-> Team A'
WHEN ORDER_ID <= 40 THEN '-> Team B'
WHEN ORDER_ID <= 60 THEN '-> Team C'
WHEN ORDER_ID <= 80 THEN '-> Team D'
WHEN ORDER_ID <= 100 THEN '-> Team E'
WHEN ORDER_ID <= 120 THEN '-> Team F'
WHEN ORDER_ID <= 140 THEN '-> Team G'
WHEN ORDER_ID <= 160 THEN '-> Team H'
WHEN ORDER_ID <= 200 THEN '-> Team I'
ELSE 'UnKnown'
END AS STATUS
FROM ORDERS;

In the above example, “STATUS” and “ORDER_ID” columns of “ORDERS” tables have been used for multiple conditions.

Output

The output depicts that the result set retrieved the data based on the applied conditions.

Conclusion

In the Oracle database, the CASE statement allows the user to execute multiple conditions in a single query and provides the result according to the conditions. Each condition in the CASE statement is added using a WHEN clause and an optional ELSE. This post has explained the use of CASE statements with multiple conditions on single or multiple columns.

Can a CASE Statement Have Multiple Conditions?