Solved

HAVING CLAUSE And/Or Clustered Index

Posted on 2003-11-05
5
1,483 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
*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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

743 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

16 Experts available now in Live!

Get 1:1 Help Now