The CASE Function
You can use the CASE function to provide conditional values to an expression.
Caution
You cannot use the CASE function to provide conditional execution of statements as in other programming languages.
The result of a CASE function is always a scalar value. You can use CASE in two different ways:
Select a result depending on the possible values that a variable, column, or expression can have. This is called Simple CASE. CASE returns the value correspondent to the first value that is equal to the searched expression. Listing 11.16 contains three examples using the CASE function to provide a verbose output.
The CASE function evaluates in sequence several independent conditions. The CASE function returns the result correspondent to the first condition that evaluates to TRUE. This is called Searched CASE. Listing 11.17 shows three examples of Searched CASE.
Code Listing 11.16. Using Simple CASE Function to Provide an Easier-to-Understand Output
|
-- Use simple CASE to expand abbreviations
SELECT CASE Country
WHEN 'UK'THEN 'United Kingdom'
WHEN 'USA'THEN 'United States of America'
ELSE Country
END AS Country,
CompanyName
FROM Customers
ORDER BY 1, 2
-- Use simple CASE to define logical values
SELECT ProductName,
CASE Discontinued
WHEN 1 THEN 'Discontinued'
ELSE 'Available'
END AS Status
FROM Products
-- Use simple CASE to produce verbose results
SELECT CompanyName,
OrderID,
CASE YEAR(OrderDate)
WHEN YEAR(Getdate()) THEN 'This Year'
WHEN YEAR(GetDate()) - 1 THEN 'Last year'
ELSE CAST(DATEDIFF(year, OrderDate, Getdate()) AS varchar(5))
+ 'years ago'
END AS 'When'
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
(Partial output)
Country CompanyName
------------------------ ----------------------------------------
United Kingdom Around the Horn
United Kingdom B's Beverages
United Kingdom Seven Seas Imports
United States of America Great Lakes Food Market
United States of America Trail's Head Gourmet Provisioners
United States of America White Clover Markets
Venezuela GROSELLA-Restaurante
Venezuela LINO-Delicateses
(Partial output)
ProductName Status
---------------------------------------- ------------
Gustaf's Knäckebröd Available
Singaporean Hokkien Fried Mee Discontinued
Wimmers gute Semmelknödel Available
(Partial output)
CompanyName OrderID When
---------------------------------------- ----------- ---------------
Furia Bacalhau e Frutos do Mar 10963 2 years ago
Furia Bacalhau e Frutos do Mar 10664 3 years ago
Furia Bacalhau e Frutos do Mar 10328 4 years ago
Princesa Isabel Vinhos 11007 2 years ago
Princesa Isabel Vinhos 10433 3 years ago
Princesa Isabel Vinhos 10336 4 years ago
Code Listing 11.17. Using Searched CASE You Can Solve Complex Queries
|
-- Use searched CASE to define regions
SELECT CASE
WHEN Country IN ('Argentina', 'Brazil', 'Venezuela')
THEN 'South America'
WHEN Country IN ('Canada', 'USA')
THEN 'North America'
WHEN Country IN ('Mexico')
THEN 'Central America'
WHEN Country IN ('Austria', 'Belgium', 'Denmark',
'Finland', 'France', 'Germany', 'Ireland', 'Italy',
'Norway', 'Poland','Portugal','Spain',
'Sweden', 'Switzerland', 'UK')
THEN 'Europe'
ELSE 'Undefined'
END AS Continent,
CompanyName
FROM Customers
-- Use searched CASE to define ranges of values
SELECT ProductName,
CASE
WHEN UnitPrice < 10 THEN 'Inexpensive'
WHEN UnitPrice < 50 THEN 'Fair'
WHEN UNitPrice < 100 THEN 'Expensive'
ELSE 'Very Expensive'
END AS Price
FROM products
-- Use searched CASE to get values that depend on data from other tables
-- in this example using a correlated subquery
SELECT CategoryName,
CASE
WHEN EXISTS (
SELECT *
FROM Products P
WHERE P.CategoryID = C.CategoryID)
THEN 'Yes'ELSE 'No'END AS 'Has products'
FROM Categories C
(Partial output)
Continent CompanyName
--------------- ----------------------------------------
Europe Around the Horn
Europe Seven Seas Imports
North America Great Lakes Food Market
North America White Clover Markets
South America GROSELLA-Restaurante
South America LINO-Delicateses
(Partial output)
ProductName Price
---------------------------------------- --------------
Gustaf's Knäckebröd Fair
Tunnbröd Inexpensive
Singaporean Hokkien Fried Mee Fair
Filo Mix Inexpensive
Gnocchi di nonna Alice Fair
(Partial output)
CategoryName Has products
--------------- ------------
Beverages Yes
Grains/Cereals Yes
Meat/Poultry No
Produce Yes
Seafood Yes
Tip
It is not necessary to create mutually exclusive conditions in a CASE function because the search will finish on the first condition that evaluates to true.