|
Execute the following
SQL Server T-SQL script in SSMS Query Editor to demonstrate the usage of CASE function in a WHERE clause:
USE AdventureWorks
GO
-- SQL case in where - SQL case function in where clause - SQL inner join
SELECT Category = c.Name,
Subcategory = sc.Name,
ProductName = p.Name,
ProductNumber,
Color = isnull(Color,''),
COST = '$' + convert(VARCHAR,StandardCost,1),
ListPrice = '$' + convert(VARCHAR,ListPrice,1),
ProductLine
FROM Production.Product p
INNER JOIN Production.ProductSubcategory sc
ON p.ProductSubcategoryID = sc.ProductSubcategoryID
INNER JOIN Production.ProductCategory c
ON sc.ProductCategoryID = c.ProductCategoryID
WHERE p.Name LIKE (CASE ProductLine
WHEN 'R' THEN 'Road%'
WHEN 'M' THEN 'Mountain%'
WHEN 'T' THEN 'Touring%'
WHEN 'S' THEN '%Shorts%'
ELSE 'Not for sale%'
END)
ORDER BY Category,
Subcategory,
ProductName;
GO
/* Partial results
Category Subcategory ProductName ProductNumber
Accessories Bottles and Cages Mountain Bottle Cage BC-M005
Accessories Bottles and Cages Road Bottle Cage BC-R205
Accessories Panniers Touring-Panniers, Large PA-T100
Accessories Pumps Mountain Pump PU-M044
Accessories Tires and Tubes Mountain Tire Tube TT-M928
Accessories Tires and Tubes Road Tire Tube TT-R982
Accessories Tires and Tubes Touring Tire TI-T723
Accessories Tires and Tubes Touring Tire Tube TT-T092
*/
|