How To Write Case Statement in Oracle

To write a case statement in Oracle, you can use the following syntax:

sql
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END

Here is an example of a case statement that returns different results based on the value of a column:

sql
SELECT column1, column2,
CASE
WHEN column3 = 'A' THEN 'Option 1'
WHEN column3 = 'B' THEN 'Option 2'
ELSE 'Option 3'
END AS result
FROM table_name;

In this example, the case statement checks the value of column3. If it is 'A', it returns 'Option 1'. If it is 'B', it returns 'Option 2'. Otherwise, it returns 'Option 3'. The result is aliased as 'result' in the query.

You can also use a case statement in the WHERE clause to filter rows based on certain conditions. Here is an example:

sql
SELECT column1, column2
FROM table_name
WHERE
CASE
WHEN column3 = 'A' THEN 1
WHEN column3 = 'B' THEN 2
ELSE 3
END > 1;

In this example, the case statement checks the value of column3. If it is 'A', it returns 1. If it is 'B', it returns 2. Otherwise, it returns 3. The WHERE clause filters rows where the case statement result is greater than 1.

You can also use a case statement in conjunction with other functions or expressions to perform more complex operations.

Professional Academic Writing Service 👈

Check our previous article: How To Write Call Options