Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

case statement in having clause in sql

Tags:

sql

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

Below 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

like image 996
Amit Avatar asked Nov 17 '10 13:11

Amit


People also ask

Can you use a case statement in a having clause?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Can I use case in FROM clause SQL?

No, you can't pick a table to query using a CASE statement. CASE statements only go within expressions, such as for a column's value or as part of your WHERE expression.

Can we use CASE statement in order by clause in SQL Server?

Although it is most often used there, CASE is not limited to SELECT statements. For example, you can use it in clauses like IN , WHERE , HAVING , and ORDER BY . Using a CASE statement in a query once doesn't mean you have hit your quota for using it. You can use it multiple times in a single query.

What is having clause in SQL with example?

In MSSQL, the HAVING clause is used to apply a filter on the result of GROUP BY based on the specified condition. The conditions are Boolean type i.e. use of logical operators(AND, OR). This clause was included in SQL as the WHERE keyword failed when we use it with aggregate expressions.


2 Answers

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;
like image 126
Mitch Wheat Avatar answered Nov 02 '22 23:11

Mitch Wheat


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?

like image 39
D'Arcy Rittich Avatar answered Nov 03 '22 00:11

D'Arcy Rittich