May 6, 2008 at 2:28 pm
I'm running a query into a temp table and it runs successfully but it displays the warning message when it finishes. The temp table is generated successfully, though.
Does anybody have any idea what may be causing this message and how to avoid it.
thx,
John
May 6, 2008 at 2:31 pm
And, what perchance, is this warning message?
😎
May 6, 2008 at 2:32 pm
Usually this means that your statement contains aggregate functions -- such as SUM, MAX or MIN -- that are operating on NULL values. As you have seen most likely your code is functioning correctly. Sometimes you can be surprised how aggregates such as AVG might work under these circumstances. You might want to research this a bit in books online just so that you are comfortable with what is going on.
May 6, 2008 at 2:34 pm
Message below:
Warning: Null value is eliminated by an aggregate or other SET operation.
May 6, 2008 at 2:39 pm
The results of this query are the expected results:
select
count(*) as theCount,
avg(n) as theAverage
from
( select 1.0 as n union all
select 3.0 union all
select null
) a
/* -------- Sample Output: --------
theCount theAverage
----------- ---------------------------------------
3 2.000000
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
*/
NOTE however that the average is 2.0 and NOT 1.333333333. This sometimes surprises people but it is the expected behavior.
May 7, 2008 at 7:46 am
Just another view of the example above. I added a count(n) to show the difference between count(*) and count(column_name).
select
count(*) as theCount,
count(n) as theOtherCount,
avg(n) as theAverage
from
( select 1.0 as n union all
select 3.0 union all
select null
) a
sample output
theCount theOtherCount theAverage
----------- ------------- ---------------------------------------
3 2 2.000000
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
😎
May 7, 2008 at 8:49 pm
If to add a simple instruction:
SET ANSI_WARNINGS OFF
select
count(*) as theCount,
count(n) as theOtherCount,
avg(n) as theAverage
from
( select 1.0 as n union all
select 3.0 union all
select null
) a
SET ANSI_WARNINGS ON
we'll get the output:
theCount theOtherCount theAverage
-------- ------------- ---------------------------------------
3 2 2.000000
(1 row(s) affected)
No warning message here.
_____________
Code for TallyGenerator
May 8, 2008 at 12:56 am
If speed it of importance, you may not want to use SET ANSI_WARNINGS OFF.
Why? Beacuse all settings altering the current connection triggers a recompile.
http://www.novicksoftware.com/Articles/minimizing-stored-procedure-recompilation-page2.htm
Quote from Experts-Exchange "performance killer"
In SQL 2000 I have an SP that gets called all the time from a .NET web application. I just discovered that when I run it in QA, I get warnings because of a RIGHT JOIN that sometimes returns NULL's when there are no matches in the left table. I hunted around and tried adding "SET ANSI_WARNINGS OFF" at the beginning of the SP, and "SET ANSI_WARNINGS ON" at the end of the SP. Cleared it up. But now I find the SP is taking a *lot* longer to run.
Sorry for the long preface. If I set the warnings OFF, will they stay off until I set them on? And is there any penalty to just leaving the warnings off (or on for that matter, given the .net web interface)?
http://www.sqlservercentral.com/articles/Administering/ansioptionspart4ansi_warnings/786/
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply