Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Searched CASE expression (CASE STATEMENT)

Searched CASE expression (CASE STATEMENT):

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression

Syntax:

CASE

WHEN Boolean_expression1 THEN Result1

WHEN Boolean_expression2 THEN Result2

ELSE ResultN

END

Example:

Employee Table Data:

  

SELECT EmployeeName,Country,CompanyPlant,

CASE  WHEN Gender =1 THEN 'Male'

      WHEN Gender =2 THEN 'Female'

      ELSE 'MiddleSex'

END  AS Gender FROM Employee  


After executing T-SQL query :


Simple CASE Expression (CASE STATEMENT)

Simple CASE Expression (CASE STATEMENT):

Allows only set of simple expression (equality check), no other comparisons are made. First expression compares with when clause expression, If these expressions are equivalent, the expression in the THEN clause will be returned, else control passed to next when clause expression if exist.

If no equivalent expression in when clause then controlled passed to else clause and returns else clause expression value.

Syntax:

CASE expression

WHEN expression1 THEN Result1

WHEN expression2 THEN Result2

ELSE ResultN

END

Example:

Employee table data:


SELECT EmployeeName,Country,CompanyPlant,

CASE Gender

 WHEN 1 THEN 'Male'

 WHEN 2 THEN 'Female'

 ELSE 'MiddleSex'

END  AS Gender FROM Employee 

After executing T-SQL query :


Having Clause with CASE expression

Having Clause with CASE expression

Introduction

My friend asked me a question, “Can I use case statement in a HAVING clause in SQL server?”

I was confused but still answered I think this is possible.

After some tweaks I crated t-sql query that uses case statement in having clause and that experience I shared in this tutorial.

Example:

In this example select statement returns employee with Searched CASE expression which satisfies following condition.

Condition:

Male employee salary is more than “INR 150” per day and female employee salary is more than “INR 180.”

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Code Sample:

Employee table Data:

 

 

--- Searched CASE expression

SELECT EmployeeName ,Country,CompanyPlant,Gender, Total=MAX(PayScale)

FROM Employee

GROUP BY EmployeeName ,Country,CompanyPlant,Gender

HAVING (MAX(CASE WHEN Gender = 'Male'

 THEN PayScale

 ELSE NULL END) > 150.00

 OR MAX(CASE WHEN Gender = 'Female'

 THEN PayScale

 ELSE NULL END) > 180.00)

 

Select Query returned Data: