Null value is eliminated by an aggregate or other

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)

Null value is eliminated by an aggregate or other

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-03-05 : 12:02:50

What does this mean?
"Null value is eliminated by an aggregate or other SET operation."

I am getting this result with this code:


SELECT T4.DivisionID, T4.SubDivisionID, T4.DeptID, T1.ACCOUNT_NBR10, 
	T2.MY_IND, T1.EXPB_REVB_IND,
	CASE WHEN Sum([SumOfRECOGNIZED_AMT]) IS NULL THEN 0 ELSE Sum([SumOfRECOGNIZED_AMT]) END AS Recog,
	CASE WHEN Sum([SumOfFINAL_BGT]) IS NULL THEN 0 ELSE Sum([SumOfFINAL_BGT]) END AS Final
FROM (( #BPSM_Web_RB T1 LEFT JOIN #ORGN T2 ON T1.ACCOUNT_NBR10 = T2.ACCOUNT_NBR) 
LEFT JOIN #REVB_TOTAL T3 ON T1.ACCOUNT_NBR10 = T3.ACCOUNT_NBR) 
LEFT JOIN #DIV_SUBDIV_DEPT_ACCT T4 ON T1.ACCOUNT_NBR10 = T4.AccountNum
GROUP BY T4.DivisionID, T4.SubDivisionID, T4.DeptID, T1.ACCOUNT_NBR10, T2.MY_IND, T1.EXPB_REVB_IND
HAVING T2.MY_IND<>'Y' AND T1.EXPB_REVB_IND='RB'

I am getting the results but still donot know if this would throw an error while running the appln.

Any help will be appreciated.

Thank you.
PKS.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-05 : 12:07:21

some apps will give you an error here.

This should work though. - give it a try

--*********************************************
SELECT T4.DivisionID, T4.SubDivisionID, T4.DeptID, T1.ACCOUNT_NBR10,
T2.MY_IND, T1.EXPB_REVB_IND,
Sum(isnull([SumOfRECOGNIZED_AMT], 0)) AS Recog,
Sum(isnull([SumOfFINAL_BGT], 0)) AS Final
FROM (( #BPSM_Web_RB T1 LEFT JOIN #ORGN T2 ON T1.ACCOUNT_NBR10 = T2.ACCOUNT_NBR)
LEFT JOIN #REVB_TOTAL T3 ON T1.ACCOUNT_NBR10 = T3.ACCOUNT_NBR)
LEFT JOIN #DIV_SUBDIV_DEPT_ACCT T4 ON T1.ACCOUNT_NBR10 = T4.AccountNum
GROUP BY T4.DivisionID, T4.SubDivisionID, T4.DeptID, T1.ACCOUNT_NBR10, T2.MY_IND, T1.EXPB_REVB_IND
HAVING T2.MY_IND<>'Y' AND T1.EXPB_REVB_IND='RB'

Duane.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-05 : 12:11:37

Use this:

quote:
From BOL
SET ANSI_WARNINGS affects these conditions:

When ON, if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) a warning message is generated. When OFF, no warning is issued.

SET ANSI_WARNINGS OFF

SELECT T4.DivisionID, T4.SubDivisionID, T4.DeptID, T1.ACCOUNT_NBR10, 
	T2.MY_IND, T1.EXPB_REVB_IND,
	CASE WHEN Sum([SumOfRECOGNIZED_AMT]) IS NULL THEN 0 ELSE Sum([SumOfRECOGNIZED_AMT]) END AS Recog,
	CASE WHEN Sum([SumOfFINAL_BGT]) IS NULL THEN 0 ELSE Sum([SumOfFINAL_BGT]) END AS Final
FROM (( #BPSM_Web_RB T1 LEFT JOIN #ORGN T2 ON T1.ACCOUNT_NBR10 = T2.ACCOUNT_NBR) 
LEFT JOIN #REVB_TOTAL T3 ON T1.ACCOUNT_NBR10 = T3.ACCOUNT_NBR) 
LEFT JOIN #DIV_SUBDIV_DEPT_ACCT T4 ON T1.ACCOUNT_NBR10 = T4.AccountNum
GROUP BY T4.DivisionID, T4.SubDivisionID, T4.DeptID, T1.ACCOUNT_NBR10, T2.MY_IND, T1.EXPB_REVB_IND
HAVING T2.MY_IND<>'Y' AND T1.EXPB_REVB_IND='RB'

SET ANSI_WARNINGS ON

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-05 : 12:12:27

It's not an error, it is a warning message. It's basically saying, the column you wanted to sum up had one or more nulls in it, which were ignored. Simply because, anything plus null will return null, and hence a single null value in the column could cause the entire result to be just NULL.

Also, this expression:
CASE WHEN Sum([SumOfFINAL_BGT]) IS NULL THEN 0 ELSE Sum([SumOfFINAL_BGT]) END AS Final

can be re-written as
ISNULL (Sum([SumOfFINAL_BGT]), 0)

OS

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-05 : 12:18:05

quote:
Originally posted by mohdowais
ISNULL (Sum([SumOfFINAL_BGT]), 0)

OS

Just curious........

would this really work seeing as though the agregate is inside the isnull function?

My solutiion has the isnull function inside the agregate.

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-05 : 12:27:20

seems as if it does not.
I tested both solutions and the one with the isnull outside the agregate gives the warning.

--**********************************************
create table #temp(col1 int, col2 int);

insert into #temp values(1, 5)
insert into #temp Values(1, null)
insert into #temp Values(1,1)
insert into #temp Values(2, 2)
insert into #temp Values(2, null)
insert into #temp Values(3, null)
insert into #temp Values(3,4)
insert into #temp Values(3,5)

select col1, isnull(sum(col2), 0)
from #temp
group by col1

select col1, sum(isnull(col2, 0))
from #temp
group by col1

Duane.

sahu74
Posting Yak Master

100 Posts

Posted - 2004-03-05 : 12:37:52

Thank you all for the swift response.
PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources