Hello guys,
Hope you are doing well. I am back once again with a detailed blog on Oracle CASE function.
This is my favourite function whenever I need to evaluate multiple conditions. It is same as if-then-else PL/SQL block (we will discuss it in up-coming PL/SQL blog series) but we can use it in SQL statements.
When we use CASE in any SELECT statement, it adds an additional column to the output and we can name that column whatever we want after the AS keyword. But don’t forget, this column is not added to the table definition. It is added just in the output for this particular SELECT statement.
In order to use, there are two types of CASE functions. We will discuss them one by one.
1. Simple_case_expression:=
Syntax:
ELSE else_expr END as “col_name”
In a simple CASE expression, Oracle Database searches for the first WHEN … THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN…THEN pairs meet this condition and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.
In the above syntax, expr is a column of a table and comparison_expr, return_expr and else_expr can be column name of a table or any specific char/number value. Also, we can apply any functions as per the requirement.
For the demonstration, I have created a very simple table COUNTRY_NAME with only single column which is Country_id in Oracle database.
Let’s check that table first.
SELECT * FROM COUNTRY_NAME;
Output…
COUNTRY_ID
----------------
IN
AU
UK
BR
NL
So let’s add another column COUNTRY using the CASE function in the SELECT query and see how it works.
SELECT
country_id,
CASE country_id
WHEN 'IN' THEN 'INDIA'
WHEN 'AU' THEN 'AUSTRALIA'
WHEN 'UK' THEN 'UNITED KINGDOM1'
WHEN 'BR' THEN 'BRAZIL'
ELSE 'UNKNOWN'
END
AS country
FROM
country_name;
Output…
COUNTRY_ID COUNTRY
---------- --------------
IN INDIA
AU AUSTRALIA
UK UNITED KINGDOM
BR BRAZIL
NL UNKNOWN
You can see in the output that we have a country column which has country names for all country_ids but not for NL. It is because in the CASE function, we have not provided comparison_expr as NL so CASE function added else_expr as UNKNOWN.
2. Searched_case_expression:=
Syntax:
ELSE else_expr END as “col_name”
In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.
Have a look at the below code snippet. SQL statement is little different than the previous one but the Output is same.
SELECT
country_id,
CASE
WHEN country_id = 'IN' THEN 'INDIA'
WHEN country_id = 'AU' THEN 'AUSTRALIA'
WHEN country_id = 'UK' THEN 'UNITED KINGDOM'
WHEN country_id = 'BR' THEN 'BRAZIL'
ELSE 'UNKNOWN'
END
AS country
FROM
country_name;
Output…
COUNTRY_ID COUNTRY
---------- --------------
IN INDIA
AU AUSTRALIA
UK UNITED KINGDOM
BR BRAZIL
NL UNKNOWN
Let’s do one more example for better understanding of Search case expression.
Do you remember PRODUCT_INFORMATION table which we created while learning COALESCE Function? If you haven’t read yet, please click here and do read.
So today, we will try to achieve the same output using CASE function.
SELECT
product_id,
list_price,
min_price,
CASE
WHEN list_price IS NOT NULL THEN ( 0.9 * list_price )
WHEN list_price IS NULL
AND min_price IS NOT NULL THEN min_price
ELSE 5
END
AS "Sale"
FROM
product_information;
Output…
PRODUCT_ID LIST_PRICE MIN_PRICE Sale
---------- ---------- ---------- ----------
1769 48 43.2
1770 73 73
2378 305 247 274.5
2382 850 731 765
3355 5
Please comment below and let me know if there are any differences in the out output.
If you have any questions please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group and do like my facebook page.
Thank you,
Kapil