Case Expression

Format of CASE expression

The CASE expression has following two formats:

1.   Simple CASE expression

This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression with in the WHEN clause is matched, the expression in the THEN clause will be returned.

Syntax

1.                 CASE expression

2.                 WHEN expression1 THEN Result1

3.                 WHEN expression2 THEN Result2

4.                 ELSE ResultN

5.                 END

2.   Searched CASE expressions

This expression evaluates a set of Boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR with in each Boolean expression.

Syntax

1.                 CASE

2.                 WHEN Boolean_expression1 THEN Result1

3.                 WHEN Boolean_expression2 THEN Result2

4.                 ELSE ResultN

5.                 END

SELECT statement with CASE expressions

--Simple CASE expression: 

SELECT EmpFirstName, EmpState=(CASE StateCode

 WHEN 'TN' THEN 'Tamil Nadu' 

 WHEN 'MH' THEN 'Maharahtra' 

 ELSE NULL 

 END)

FROM dbo.Employee

-- Searched CASE expression:

SELECT FirstName,EmpState=(CASE 

 WHEN StateCode = 'TN' THEN 'Tamil Nadu' 

 WHEN StateCode = 'MH' THEN 'Maharahtra' 

 ELSE NULL 

 END)

FROM dbo.Employee

Update statement with CASE expression

-- Simple CASE expression: 

UPDATE Customer SET   EmpState=(CASE StateCode

 WHEN 'TN' THEN 'Tamil Nadu' 

 WHEN 'MH' THEN 'Maharahtra' 

 ELSE NULL 

 END

-- Simple CASE expression: 

 UPDATE Customer 

 SET StateCode = CASE 

 WHEN StateCode = 'TN' THEN 'Tamil Nadu' 

 WHEN StateCode = 'MH' THEN 'Maharahtra' 

 ELSE NULL 

         END

ORDER BY clause with CASE expressions

-- Simple CASE expression: 

SELECT * FROM dbo.Employee

ORDER BY 

 CASE Gender WHEN 'M' THEN FirstName END Desc,

 CASE Gender WHEN 'F' THEN LastName END ASC

-- Searched CASE expression: 

SELECT * FROM dbo. Employee

ORDER BY 

 CASE WHEN Gender='M' THEN FirstName END Desc,

 CASE WHEN Gender='F' THEN LastName END ASC

Having Clause with CASE expression

-- Simple CASE expression: 

SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)

FROM dbo.Customer

GROUP BY StateCode,Gender,FirstName

HAVING (MAX(CASE Gender WHEN 'M' 

 THEN PayRate 

 ELSE NULL END) > 180.00

 OR MAX(CASE Gender WHEN 'F' 

 THEN PayRate 

 ELSE NULL END) > 170.00)

-- Searched CASE expression: 

SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)

FROM dbo.Customer

GROUP BY StateCode,Gender,FirstName

HAVING (MAX(CASE WHEN Gender = 'M' 

 THEN PayRate 

 ELSE NULL END) > 180.00

 OR MAX(CASE WHEN Gender = 'F' 

 THEN PayRate 

 ELSE NULL END) > 170.00)