Saturday, March 29, 2008

SQL Case Statements

SQL Case statement can be used in 2 forms:

  1. SQL Case statement with simple expression to compare and get results.
  2. SQL Case statement with search or comparison expression to get results.

1. SQL Case statement with simple expression:
You can use default Northwind Database to test the functionality and syntax of SQL case statement.





Syntax:

USE NORTHWIND

SELECT
'Title' =
CASE TITLEOFCOURTESY
WHEN 'MS.' THEN 'MISSUS'
WHEN 'MRS.' THEN 'MISTRESS'
WHEN 'MR.' THEN 'MISTER'
WHEN 'DR.' THEN 'DOCTOR'
ELSE ''
END,
TITLEOFCOURTESY,
FIRSTNAME,
LASTNAME
FROM EMPLOYEES



Output:

Title

TitleOfCourtsey

FirstName

LastName

Missus

Ms.

Nancy

Davolio

Doctor

Dr.

Andrew

Fuller

Missus

Ms.

Janet

Leverling

Mistress

Mrs.

Margaret

Peacock

Mister

Mr.

Steven

Buchanan

Mister

Mr.

Michael

Suyama

Mister

Mr.

Robert

King

Missus

Ms.

Laura

Callahan

Missus

Ms.

Anne

Dodsworth

2. 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

2 comments:

pravin mahit said...

can u tell me how to multiple case statement in one query?

thanks in advance

Joe said...

Good one thanks!!

See this one too:

Searched case expression sql