Solved

HAVING CLAUSE And/Or Clustered Index

Posted on 2003-11-05
5
1,492 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

822 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