Case expression in sql condition

Question

Is it possible to have a case statement in a HAVING in SQL Server 2005?

Below is my HAVING statement. This gives me a syntax error.

 @CLIENTPK_NEW IS NULL OR ( CLIENT.OH_PK = @CLIENTPK_NEW and CASE WHEN @RelatedOrgs <> '11' then CLIENT.OH_PK= @CLIENTPK_NEW ELSE CLIENT.OH_PK in ( SELECT dbo.OrgHeader.OH_PK FROM dbo.OrgHeader WITH (NOLOCK) INNER JOIN dbo.OrgRelatedParty WITH (NOLOCK) ON dbo.OrgHeader.OH_PK = dbo.OrgRelatedParty.PR_OH_Parent INNER JOIN dbo.OrgHeader AS OrgHeader_1 WITH (NOLOCK) ON dbo.OrgRelatedParty.PR_OH_RelatedParty = OrgHeader_1.OH_PK where OrgHeader_1.OH_PK = @CLIENTPK_NEW ) END ) } AND (@CGNEEPK IS NULL OR CGNEE.OH_PK = @CGNEEPK) AND part.OP_RH_NKCommodityCode = @type 

Thanks,

Amit

Answer 1

Example (from here ):

 USE AdventureWorks2008R2; GO SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID GROUP BY JobTitle HAVING (MAX(CASE WHEN Gender = 'M' THEN ph1.Rate ELSE NULL END) > 40.00 OR MAX(CASE WHEN Gender = 'F' THEN ph1.Rate ELSE NULL END) > 42.00) ORDER BY MaximumRate DESC; 

Answer 2

Yes, this is a valid syntax. However, text, graphic, and ntext data types cannot be used in a HAVING .

Update: Your updated example does not make sense. Either CLIENT.OH_PK=@CLIENTPK _NEW or not, the rest of the statement doesn't matter unless you use OR . Perhaps you can explain the logic of logic?

Answer 3

another example from here ..

 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)