Using the CASE Expression in Oracle: Examples and Syntax

Here's an example and syntax for using the CASE expression in Oracle:

In Oracle, the CASE expression allows you to add conditional logic to your queries. You can use the CASE expression to perform different operations based on a set of conditions. Here's the syntax for using the CASE expression in Oracle:

sql
CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

here's another example of using the CASE expression in Oracle:

Suppose you have a table orders with columns order_id, order_date, total_amount, and discount. You want to create a new column discounted_total which will show the total amount after applying the discount, and if no discount is applied, it will show the regular total amount. Here's how you can achieve this using the CASE expression:

vbnet
SELECT order_id, order_date, total_amount, discount,
       CASE 
          WHEN discount IS NULL THEN total_amount
          ELSE total_amount - (total_amount * discount / 100)
       END AS discounted_total
FROM orders;

In this example, we are using the CASE expression to check if the discount column is null or not. If the discount column is null, we are returning the total_amount as the discounted_total. If the discount column has a value, we are applying the discount to the total_amount and returning the discounted value as the discounted_total.