Warning: Null value eliminated from aggregate.
-
I am getting this error message as a result. How do I eliminate this error?
Warning: Null value eliminated from aggregate.
I am using SET NOCOUNT ON before and after the sproc so that only my results are used but to no avail... part of the procedure uses an aggregate function like this..
SUM(CASE WHEN FailCode = 'ACT'
AND FC.OnDate < @OpenThroughDate
AND ClosedDate >=@ClosedSinceDate THEN 1
ELSE Null
END) AS ACT,
Is the NULL the part that is removed?
-Francisco
-Francisco -
The fact that you're returning a NULL inside your aggregate function is why the warning (please note that it is a warning and not an error) is being generated. For the purposes of what you are doing, you should be returning a 0 in the ELSE clause of your CASE statement instead of a NULL. That will eliminate your error and accomplish the desired results.
Matthew Burr
-
Thanks for the reply. I never anticipated any problems with this syntax because in fact I have another procedure with some variying paramters that uses the same syntax for that case statement. The sproc feeds a report in an Access 2000 ADP (sql front end hook). The initial version of the procedure followed a complex Select statment, but that was taking well over 1 minute of execution time. It was particularly this long latency that was causing Access to timeout. I figured that since each half of the procedure (from the Right outer join) would execute in about 8 seconds, I had to figure out a diffrent method. The route I chose was dumping the data into a temp table, but that's when I noticed this error. Access receives the message that the 'Null Value was eliminated...' and thus returns an error message that I cannot trap for. I've done exactly what you say, which is to make the ELSE a 0 instead of a Null and that has solved the problem. One cosmetic side-effect is that now I need to modify the gui part of the report to blank out the 0's as "blank" as they were before. ho hum
-Francisco
-Francisco -
Depending on what you're trying to achieve, changing the nulls to zeros may impact the result in a way you dont want. The other part is - why can't you trap the error in Access?
Andy
-
In the case above it would be the SUM of 0 so should not have an adverse effect. As it will be 1 or 0 and 0 adds nothing to the sum.
-
quote:
Depending on what you're trying to achieve, changing the nulls to zeros may impact the result in a way you dont want. The other part is - why can't you trap the error in Access?it seems that the results are correct when chaging the null to a 0. Honestly I don't know why this error is being trapped by Access and I can't get to it. The FE (Front End) is an Access ADP not a standard MDB. In 2000 the report does not have a .recordset property thus I can't just run it from an ADO command object and then just set the recordset... if it were XP that would be a diffrent story :(. alas I gotta make due with what I've got... I was hoping to trap the error in TSQL and suppress it from occurring but if I can't I've already worked a workaround in access tho because it is an OLE automation task it adds like 5 more seconds
-Francisco
-Francisco -
It is being trapped becaue NULL is not a value and that warning is thrown when doing SUMS and other calculation related functions.
-
see the docs in BOL for SET ANSI_NULLS {ON | OFF}
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply