Help getting rid of null warning

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help getting rid of null warning

Author

Topic

sureshot
Yak Posting Veteran

72 Posts

Posted - 2006-01-23 : 20:12:03

I'm getting the ansi warning: "Warning: Null value is eliminated by an aggregate or other SET operation.". I'd like to avoid turning off ANSI_WARNINGS and rewrite the statement to avoid the message. Here it is:


select m.user_id,Sum([weight]*[assgnaverage])/Sum(weight) end as [cataverage]
    from crmembers m left outer join
    (
    SELECT r.user_id, h.weight, AVG(r.score) AS assgnaverage
    from data h
    inner join dbo.dataresults r ON h.data_id = r.data_id
    WHERE  h.cr_id=2500
    GROUP BY r.user_id, h.data_id, h.weight
    ) as drv on m.user_id=drv.user_id
    WHERE m.cr_id=2500 and m.status=1
    GROUP BY m.user_id, drv.user_id

It's giving the error predictably where a row in crmembers exists with no corresponding row in the derived table drv.

I had tried the following modified thinking it should solve the issue and I'm not sure why it didn't.


    select m.user_id,CASE WHEN drv.user_id is null then null else Sum([weight]*[assgnaverage])/Sum(weight) end as [cataverage]
    from crmembers m left outer join
    (
    SELECT r.user_id, h.weight, AVG(r.score) AS assgnaverage
    from data h
    inner join dbo.dataresults r ON h.data_id = r.data_id 
    WHERE  h.cr_id=2500
    GROUP BY r.user_id, h.data_id, h.weight
    ) as drv on m.user_id=drv.user_id
    WHERE m.cr_id=2500 and m.status=1 
    GROUP BY m.user_id, drv.user_id

Any suggestions at to why this didn't work and successful ways to rewrite it?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-23 : 20:23:17

I think u may have to use for each fiel in calculation the following:
isnull(field,0)

so ur field "weight" will be isnull(weight,0) and so on.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 21:18:38

try this

select m.user_id, Sum([weight]*[assgnaverage])/Sum(weight) end as [cataverage]
from   crmembers m left outer join
(
    SELECT r.user_id, isnull(h.weight, 0) , AVG(isnull(r.score, 0) AS assgnaverage
    from  data h
    inner join dbo.dataresults r ON h.data_id = r.data_id
    WHERE  h.cr_id=2500
    GROUP BY r.user_id, h.data_id, h.weight
) as drv 
on     m.user_id = drv.user_id
WHERE  m.cr_id=2500 and m.status=1
GROUP BY m.user_id, drv.user_id

----------------------------------
'KH'
I do work from home but I don't do homework

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-24 : 11:11:35

quote:

Originally posted by khtan

try this

select m.user_id, Sum([weight]*[assgnaverage])/Sum(weight) end as [cataverage]
from   crmembers m left outer join
(
    SELECT r.user_id, isnull(h.weight, 0) , AVG(isnull(r.score, 0) AS assgnaverage
    from  data h
    inner join dbo.dataresults r ON h.data_id = r.data_id
    WHERE  h.cr_id=2500
    GROUP BY r.user_id, h.data_id, h.weight
) as drv 
on     m.user_id = drv.user_id
WHERE  m.cr_id=2500 and m.status=1
GROUP BY m.user_id, drv.user_id

----------------------------------
'KH'
I do work from home but I don't do homework

this: AVG(isnull(r.score, 0))
is different than: AVG(r.score) (especially if r.score can be null)

quote:
From BOL:Returns the average of the values in a group. Null values are ignored.

to calculate the same average you would need something along the lines of:
...
sum(case when r.Score is not null then r.Score else 0 end) / sum(case when r.Score is not null then 1 else 0 end)
...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

sureshot
Yak Posting Veteran

72 Posts

Posted - 2006-01-24 : 11:11:58

That didn't help. The inner SQL is not giving the error if I take it out. It's this part that's giving the problem:

Sum([weight]*[assgnaverage])/Sum(weight)

The cataverage is supposed to be null at times so I don't want to make it all zero.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-24 : 11:22:31

if it is supposed to be null sometimes... then you will continue to get the error. An aggregate result should not be null sometimes, as the result will not always reflect the entire dataset.

I would suggest removing all of the nulls, and substituting -1 (or something) for the 'null' case...

or you could just ignore the warning.

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Kristen
Test

22859 Posts

Posted - 2006-01-24 : 13:57:39

If I make an average of a bunch of rows - where some are zero, some have a value, and some are NULL - generally I want to exclude the NULLs from the average. Its a more complicated calculation, but usually worth it!

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-24 : 14:09:19

I'm not saying that it isn't useful... but he is claiming he doesn't want the warning... I would accept the warning if the null exclusion was what I wanted...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

sureshot
Yak Posting Veteran

72 Posts

Posted - 2006-01-24 : 16:38:33

I wanted to exclude the warning because in classic ASP/ADO, the warning was returned as my first recordset and wreaked havoc on my logic. I had resigned myself to calling "SET ANSI_WARNINGS OFF;EXEC <spcall>" to avoid this.

But from initial tests, it looks like in ADO.NET2, this is no longer the case. The warning seems to not appear anywhere. I'm not sure where I'd find it even if I wanted it...

So I think I'm ok to just leave this as is. Thanks for helping me investigate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources