Oracle CASE statement || SQL CASE statement with 2 Example

Oracle CASE statement

The SQL CASE statement is a logical statement that helps in putting values based on logical arguments. The CASE statement goes through conditions and returns a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If no conditions meet the criteria then NULL value will return.

CASE Syntax in SQL

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In the above SQL CASE syntax, we can see it starts with CASE then WHEN followed by condition1. If this condition is met then result1 will show otherwise SQL query will move to condition2 and if this condition is met then it will show result2. Again if this condition doesn’t meet then it will move to the next condition. In case all the conditions don’t meet then it will show results from the ELSE statement.

Oracle SQL case with one condition

SELECT DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID,MANAGER_ID,
CASE WHEN MANAGER_ID IS NULL THEN 300 ELSE MANAGER_ID END NEW_MANAGER_ID
FROM DEPARTMENTS;

In the above SQL query statement, you can see we used one condition. The condition says if MANAGER_ID is null then put value 300 else values from MANAGER_ID. Also, notice that the condition closes with the END statement. We named this column NEW_MANAGER_ID. This new column is basically a virtual column not an actual one.

Now in the final result, we can see the new SQL column NEW_MANAGER_ID has a value of 300 instead of NULL as it meets the condition. We can reverse this condition as well in the below SQL CASE Examples.

SELECT DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID,MANAGER_ID,
CASE WHEN MANAGER_ID IS NOT NULL THEN MANAGER_ID ELSE 300 END NEW_MANAGER_ID
FROM DEPARTMENTS;

Now we can see that the new condition has been reversed. It says if MANAGER_ID is not null then use values from MANAGER_ID if it fails then only use the value of 300. So when it comes to null values in MANAGER_ID its condition doesn’t meet and as result, it started to put the value of 300.

Oracle CASE statement example with more than one condition

Let’s understand CASE WHEN SQL oracle with more than one SQL CASE statement.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,
CASE WHEN SALARY <=25000 THEN '0-25K'
WHEN SALARY <=50000 THEN '25K-50K'
WHEN SALARY <= 75000 THEN '50K-75K'
WHEN SALARY <= 100000 THEN '75K-100K'
ELSE '100K+' END SALARY_RANGE
FROM EMPLOYEES;

In the above oracle case statement example, we used four WHEN conditions to create a range of salaries. In the first condition, we used if the salary is less than 25000 then condition1(‘0-25k’) next if the salary is less than 50000 then ’25k-50k’ and so on. It is to be noted here when we use less than 50000 the value from 0-25000 would not be included here as it was also categorized by ‘0-25k’.

Case Statement in SQL with Group by clause

We can use CASE statement in SQL with GROUP BY clause as well. To understand this SQL example let’s see the below SQL script.

SELECT CASE WHEN SALARY <=2500 THEN 'A_0-25H'
WHEN SALARY <=5000 THEN 'B_25H-50H'
WHEN SALARY <= 7500 THEN 'C_50H-75H'
WHEN SALARY <= 10000 THEN 'D_75H-100H'
ELSE 'E_100H+' END SALARY_GROUP,count(*) TOTAL_COUNT from EMPLOYEES 
group by
CASE WHEN SALARY <=2500 THEN 'A_0-25H'
WHEN SALARY <=5000 THEN 'B_25H-50H'
WHEN SALARY <= 7500 THEN 'C_50H-75H'
WHEN SALARY <= 10000 THEN 'D_75H-100H'
ELSE 'E_100H+' END
ORDER BY SALARY_GROUP ;

In the above SQL example, we can see that first, we used CASE WHEN statement then we use SQL aggregate function COUNT(*) to get count of these groups, and in the last we used GROUP BY with these case CASE WHEN clause. The final result would be like below.

You can read more about SQL-related articles from this link or from here.

Post Views: 44,304

Oracle CASE statement || SQL CASE statement with 2 Example