Solved

HAVING CLAUSE And/Or Clustered Index

Posted on 2003-11-05
5
1,487 Views
Last Modified: 2007-12-19
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.

0
Comment
Question by:ccarns
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 150 total points
ID: 9688548
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
 
LVL 1

Author Comment

by:ccarns
ID: 9688590
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
 
LVL 32

Expert Comment

by:bhess1
ID: 9688620
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
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 250 total points
ID: 9688665
*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
 
LVL 1

Author Comment

by:ccarns
ID: 9688672
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now