case statement in having clause in sql

Can I have a case statement in a HAVING clause in SQL server 2005?

Belo开发者_JAVA百科w is my HAVING statement. It is giving 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

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;

Yes, that is valid syntax. However, the text, image, and ntext data types cannot be used in a HAVING clause.

Update: Your updated example does not make sense. Either CLIENT.OH_PK=@CLIENTPK_NEW or it doesn't, the rest of the statement is irrelevent, unless you use an OR. Perhaps you can explain the busingess logic?

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)