Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

HAVING CLAUSE And/Or Clustered Index

Posted on 2003-11-05
5
Medium Priority
?
1,504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 450 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:Brendt Hess
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:Brendt Hess
Brendt Hess earned 750 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

610 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