How to correct a NULL issue when summing values

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

General SQL Server Forums

New to SQL Server Programming

How to correct a NULL issue when summing values

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-15 : 23:41:34

I currently have a program (see below) which creates a new table called PRODUCT and then inserts fields into it from 2 other tables (ACCOUNTS & BASECASE) . The program works fine, but I get an error entitled
"Warning: Null value is eliminated by an aggregate or other SET operation. The statement has been terminated."

I know this is probably happening because fields s.addamt and s.ReprRate may contain nulls in the ACCOUNTS table where the fields are defined as Floating Nulls. I think because this program reads over 1.5 million records, it fails on this error. Is there a way to get around this?


-- Creates the product table

create table Product (scenario char(20), Name char(50), ScenIndex smallint, SumOfNotional float, SumOfAddAmt float,WavgReprRate float)


-- Inserts into product table by storing either the sum or wavg of the field

INSERT INTO Product
(Scenario, Name, ScenIndex, SumOfNotional, SumOfAddAmt, WavgPriceRate)

SELECT 'Base' as Scenario,w.Name,s.ScenIndex,sum(s.notional),
sum(s.addamt),sum(s.[ReprRate] *s.[eopbal])/sum(s.[eopbal])

from dbo.ACCOUNTS as W Inner join BASECASE as s on w.id=s.accountid
group by w.name, s.scenIndex

Sachin.Nand

2937 Posts

Posted - 2010-07-16 : 00:53:47

I dont think it can be classified as an error.It is just a warning.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 01:24:01

Thanks Idera for your feedback. But why does the sql program stop running then?

I tried running once more and now in addition to the warning error I receive:

Server Msg 8134, Level 16, State 1, Line 22
Divide by Zero error occurred.
Warning: Null value is eliminated by an aggregate or other SET operation. The statement has been terminated.

Kristen
Test

22859 Posts

Posted - 2010-07-16 : 02:00:43

SELECT 'Base' as Scenario,w.Name,s.ScenIndex,sum(s.notional),
sum(s.addamt),sum(s.[ReprRate] *s.[eopbal])/ NullIf(sum(s.[eopbal]), 0)

Use 0.0 if eopbal if floating point datatype, rather than integer datatype

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 02:54:54

Thank you Kristen, this did the trip. It works great!

Kristen
Test

22859 Posts

Posted - 2010-07-16 : 03:00:50

if you need to get rid of the warning (because your application barfs at it) you can do

sum(COALESCE(MyColumn, 0))

Again, use 0.0 if its a floating point datatype

(You can also turn off the warning using SET ANSI_WARNINGS but I think that's a bad idea)

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 03:40:10

Wow! that took care of it. So basically the coalence command replaces the null with a 0. How perfect. Thanks again for your help.

Kristen
Test

22859 Posts

Posted - 2010-07-16 : 03:54:36

COALESCE returns the first non-null parameter from its list, and is a handy tool indeed

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-07-16 : 20:27:10

why dont you just set default values as 0's to those columns ad avoid all this headache?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2010-07-17 : 01:45:36

Because maybe the value is unknown, rather than zero?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-07-19 : 16:03:53

I understand it depends on the application just throwing out a suggestion..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources