Nested case statement in sql – Best way to learn sql nested case 5 Examples

In Nested case statement in sql, best way to learn sql nested case step by step. Understanding the SQL Server CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement Top 10+ Examples bellow.

Nested case statement in SQL

Nested case statements are a common technique in SQL. They allow you to run multiple case statements based on the same condition. For example, you could use a nested case statement to check the status of a particular database table.

The following code shows a nested case statement that checks the status of a table. The nested case statement starts by checking the status of the table named mytable. If the table is not empty, the nested case statement checks the status of the table named mytable_2.

If mytable_2 is not empty, the nested case statement checks the status of the table named mytable_3.

This nested case statement can be executed multiple times, depending on the condition that is being checked.
sort of COALESCE

SELECT COALESCE(
CASE WHEN first_condition THEN calculation1 ELSE NULL END,
CASE WHEN second_condition THEN calculation2 ELSE NULL END,
etc...
)

CASE Syntax

CASE
WHEN first_condition THEN first_result
WHEN second_condition THEN second_result
WHEN conditionN THEN resultN
ELSE result
END;

Example
The following SQL will order the members by address. However, if address is NULL, then order by mobile:

SELECT MemberName, address, mobile FROM members
ORDER BY (CASE
WHEN address IS NULL THEN mobile
ELSE address
END);

sql nested case statements

Best way to do nested case statement in sql Server

Select
(CASE WHEN REQUESTS.mobile_id = 1 THEN
(CASE WHEN ((cr_date-end_date)*24*60)<=34 THEN 'Yes'
ELSE 'No'
END)
WHEN REQUESTS.mobile_id = 2 THEN
(CASE ((cr_date-end_date)*24*60) <=658 THEN 'Yes'
ELSE 'No'
END)
WHEN REQUESTS.mobile_id = 3 THEN
(CASE ((cr_date-end_date)*24*60)<=18540 THEN 'Yes'
ELSE 'No'
END)
END)in_SLA

Here are some examples of the SQL CASE statement in SELECT queries.

CASE in a WHERE Clause

SELECT profile_nm, account_nm, location
FROM members
WHERE
(CASE
WHEN location IN ('IN', 'Rajkot') THEN 'North India'
WHEN location IN ('PK', 'France') THEN 'Pakistan'
ELSE 'NoFound'
END) = 'North India'
ORDER BY profile_nm, account_nm;

Multiple Matches

SELECT profile_nm, account_nm, members,
CASE
WHEN members < 1 THEN 'No Members'
WHEN members < 10 THEN 'Small'
WHEN members <= 50 THEN 'Medium' WHEN members >= 50 THEN 'Large'
END SizeOfCompany
FROM members
ORDER BY profile_nm, account_nm;

CASE Statement with Functions

SELECT profile_nm, account_nm, members,
CASE
WHEN MOD(members, 2) = 0 THEN 'Even Number of Members'
WHEN MOD(members, 2) = 1 THEN 'Odd Number of Members'
ELSE 'NoFound'
END OddOrEven
FROM members
ORDER BY profile_nm, account_nm;

CASE Within CASE Statement

SELECT profile_nm, account_nm, location,
CASE
WHEN location IN ('IN', 'Rajkot') THEN
(CASE WHEN profile_nm = 'Sally' THEN 'North India F' ELSE 'North India M' END)
WHEN location IN ('PK', 'France') THEN
(CASE WHEN profile_nm = 'Sally' THEN 'Pakistan F' ELSE 'Pakistan M' END)
ELSE 'NoFound'
END Continent
FROM members
ORDER BY profile_nm, account_nm;

CASE Statement With IN Clause

SELECT profile_nm, account_nm, location,
CASE
WHEN location IN ('IN', 'Rajkot') THEN 'North India'
WHEN location IN ('PK', 'Lah') THEN 'Pakistan'
ELSE 'NoFound'
END Continent
FROM members
ORDER BY profile_nm, account_nm;

Searched Case with Numbers

SELECT profile_nm, account_nm, members,
CASE
WHEN members < 10 THEN 'Small' WHEN members >= 10 AND members <= 50 THEN 'Medium' WHEN members >= 50 THEN 'Large'
END SizeOfCompany
FROM members
ORDER BY profile_nm, account_nm;

Nested “CASE when” statement

nested case statement in sql

SELECT shayriQuates.Month, shayriQuates.Year, shayriQuates.[Entity Number],
case when [Last Year] = 2022 + 1 -- why not = 2011 ?
then 'Lastdata in 1 to 5 Years' -- this does not match the logic on line above
when [Last Year] > 2022 + 5 -- why not > 2015 ?
then 'Lastdata After 5 Years'
else 'No Last end Year Listed'
end
from shayriQuates

Use a nested CASE expression

SELECT
CASE
WHEN ISNULL(products.customcode, 0) <> 0 AND ISNULL(jdk.level, 0) > 0 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS Quoted,
CASE
WHEN ISNULL(products.customcode, 0) <> 0 AND ISNULL(jdk.level, 0) > 0 THEN
CASE
WHEN a.DisplayRanks = 'LOST' THEN 'doomed'
WHEN a.DisplayRanks = 'DECLINED' THEN 'doomed_NotTakenUp'
WHEN a.DisplayRanks = 'NOT TAKEN UP' THEN 'doomed'
ELSE a.DisplayRanks
END
ELSE a.DisplayRanks
END AS DisplayRanks
FROM MyTable

Nested CASE Statement With ELSe (SQL Server)

select (CASE WHEN A IS NULL AND B IN ('C', 'D') THEN NULL
WHEN A IS NULL AND X NOT IN ('C','D') THEN Z
WHEN A IS NOT NULL THEN SOMETHING_ELSE
END) as Result

CASE is an expression, not a statement

SELECT CASE [status]
WHEN 'A' THEN
RanksLabel = 'Authorized',
LastEvent = AuthorizedTime
WHEN 'C' THEN
RanksLabel = 'Completed',
LastEvent = CompletedTime
END
FROM dbo.some_table;

Or this:

SELECT CASE WHEN @tamil = 1 THEN
(SELECT tamil, fruites FROM dbo.tamilrokers)
ELSE
(SELECT type, std FROM dbo.products)
END;

Nested Case Statement in SQL Server

select productname, job, price, case -- Outer Case
when productname like 'A%' then
case when price >= 1250 then 'A' -- Nested Case
end
when productname like 'J%' then
case when price >= 5000 then 'J' -- Nested Case
end
end as "Name-Grade"
From Emp

Multiple conditions in CASE statement

Let’s write a SQL Server CASE statement which sets the value of the condition column to “Latest” if the value in the Brand column is greater than 2022, to ‘Standard’ if the value in the Brand column is greater than 2015, and to ‘Antique’ if the value in the Brand column is greater than 1995.

SELECT name,
Brand,
CASE WHEN Brand > 2022 THEN 'Latest'
WHEN Brand > 2015 THEN 'Standard'
WHEN Brand > 1995 THEN 'Antique'
ELSE 'Antique' END AS condition
FROM Cars

However, in the above script, the conditions are overlapping as. the Brand with a value greater than 2022 also has a value greater than 2015 and 1995.

A better way to implement multiple conditions is to use logical operators like AND, OR, NOT, etc.

Look at the following script:

SELECT name,
Brand,
CASE WHEN Brand > 2022 THEN 'Latest'
WHEN Brand > 2015 AND Brand <2022 THEN 'Standard' WHEN Brand > 1995 AND Brand <2015 THEN 'Antique' ELSE 'Antique' END AS condition FROM Cars 

I can also evaluate multiple conditions from different columns using the SQL Server CASE statement. In the following example, we will assign the value of “Latest Hundai” to the condition column where the Brand is greater than 2022 and the type is hundai. Look at the following script:

 SELECT name, type, Brand, CASE WHEN Brand > 2022 AND type = 'hundai' THEN 'Latest Hundai'
WHEN Brand > 2022 THEN 'Latest'
WHEN Brand > 2015 AND Brand <2022 THEN 'Standard' WHEN Brand > 1995 AND Brand <2015 THEN 'Antique'
ELSE 'Antique' END AS condition
FROM Cars

Using GROUP BY with SQL Server CASE statement

SELECT
CASE WHEN Brand > 2015 THEN 'Latest'
ELSE 'Antique' END AS condition,
COUNT(1) AS count
FROM Cars
GROUP BY CASE WHEN Brand > 2015 THEN 'Latest'
ELSE 'Antique' END

Also Read : Nested Select Statement In SQL Server

Similarly, I can GROUP BY more than two values. Look at the following script:

SELECT
CASE WHEN Brand > 2022 THEN 'Latest'
WHEN Brand > 2015 THEN 'Standard'
WHEN Brand > 1995 THEN 'Antique'
ELSE 'Antique' END AS condition,
COUNT(1) AS count
FROM Cars
GROUP BY CASE WHEN Brand > 2022 THEN 'Latest'
WHEN Brand > 2015 THEN 'Standard'
WHEN Brand > 1995 THEN 'Antique'
ELSE 'Antique' END

In the script above mysql, I grouped the data into three categories: “Latest”, “Standard” and “Antique”.