HAVING CLAUSE And/Or Clustered Index

I am running into a limitation of the Having Clause and need to know if anybody has workaround.

It seems that SQL does not handle the IN statement  well. EXAMPLE :

....
HAVING
CASE WHEN DD.SomeField = '915' AND DD.SomeOtherField <> '1X50'  THEN DD.AnotherField ELSE 0 END

works just fine, but can not handle

....
HAVING
CASE WHEN DD.SomeField IN ('510','511','512','520','530','540','610','611')  THEN DD.AnotherField ELSE 0 END

throws an error :

Server: Msg 8121, Level 16, State 1, Procedure BLAH_VW, Line 4
Column 'DD.AnotherField' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

So I fixed it by putting it into a User Defined Function(UDF).  But now I want to create an Clustered Index on this View and SQL does not like to play nice with UDF's (non deterministic error on the UDF).

Ideally I would keep this code in a UDF and be able to create the Clustered Index - but I fear the restrictions are too heavy to accomplish.

Suggestions?  If you have links to good resources that would be great too.

LVL 1
ccarnsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
don't follow post your sql

why would you have that case in a having clause

the having clause is there to specify conditions based on aggregate functions...

anything else should be accomplished in the Where clause....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ccarnsAuthor Commented:
Sorry I missed a crucial piece in the post - and I think I just figured it out. The piece was that SUM() was suppose to be wrapped around it.  If SUM is missing it cant handle it.  

The reason there is a SUM in the having is because I need to do a filter after the data is rolled up.
0
Brendt HessSenior DBACommented:
I agree with Lowfatspread on this one.  It looks like the CASE should be in your WHERE clause.

A HAVING clause should only be used in cases where you are filtering on some aggregate value.  For example, if you had a query that included:

Sum(Payments) As TotalPayments

You could have a HAVING clause of:

HAVING TotalPayments > 100

However, if you are looking for payments made on Left-Handed Veeblefleetzers that you have sold (Product ID = 666), you would place that limitation in the WHERE Clause, e.g.:

SELECT ..., Sum(Payments) as TotalPaid, ...
FROM MyTable
WHERE ProductID = 666
GROUP BY ...
HAVING TotalPaid > 100
0
Brendt HessSenior DBACommented:
*IF* I understand what you mean (please clarify), your HAVING statement looks something like:

HAVING
SUM( CASE WHEN DD.SomeField IN ('510','511','512','520','530','540','610','611')  THEN DD.AnotherField ELSE 0 END ) > 1

(Greater than clause for illustration only)

This should be in the base query:

SELECT....
SUM(CASE WHEN DD.SomeField IN ('510','511','512','520','530','540','610','611')  THEN DD.AnotherField ELSE 0 END ) AS AnotherTotal

Then use the HAVING:
HAVING AnotherTotal > 1

I am surprised that your first sample syntax works in a HAVING clause without having the fields in an aggregate.  Personally, I would consider the fact that this works to be a bug in SQL Server 2K, since it should not work on fields in the HAVING that are not in an aggregate.

0
ccarnsAuthor Commented:
bhess1 you are correct and that is actually how I am using it -  I just need to learn how to copy and paste better  :)

Thanks for your input.


Bottom line is that I left out the SUM  ( I am dynamically generating a bunch of Views from a SP and few tables so it was a data entry error)  

Thanks for your posts.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.