Case Statement Part6

SQL Case Statement with search comparison expressions:

Syntax:

USE NORTHWIND

SELECT
'COMMENT' =
CASE
WHEN  UNITPRICE IS NULL THEN 'NO PRICE DEFINED'
WHEN  UNITPRICE < 20 THEN 'LOW PRICE'
WHEN  UNITPRICE > 20 AND UNITPRICE < 30 THEN 'REASONABLE PRICE'
ELSE 'EXPENSIVE ITEM!!!'
END,
PRODUCTNAME,
UNITPRICE
FROM PRODUCTS

Output:

Comment	ProductName	UnitPrice
Low Price	Chai	18.00
Low Price	Chang	19.00
Low Price	Aniseed Syrup	10.00
Reasonable Price	Chef Anton’s Cajun Seasoning	22.00
Reasonable Price	Chef Anton’s Gumbo Mix	21.35
Reasonable Price	Grandma’s Boysenberry Spread	25.00
Expensive Item!!!	Uncle Bob’s Organic Dried Pears	30.00
Expensive Item!!!	Northwoods Cranberry Sauce	40.00