Prevent divide by zero exception

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 2008 Forums

Transact-SQL (2008)

Prevent divide by zero exception

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-20 : 05:15:35

Hi all,
I have this statement that I'm using in a stored procedure:

DECLARE @CalorieDaCaloTotale DECIMAL(18,3)

SELECT @CalorieDaCaloTotale =
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/ISNULL(d.DQprod,0) * ISNULL(p.PCIMedio,0))/1000000,0)
from @QCali s
INNER JOIN @NumeratoreQprod n on s.Combustibili_ID = n.CombustibileID
INNER JOIN @DenominatoreQprod d on s.Combustibili_ID = d.CombustibileID
INNER JOIN @PCIMedio p on s.Combustibili_ID = p.Combustibili_ID

The problem is that I receive a divide by zero exception, because some values in the denominator are 0.

How can I change it to prevent this?

Thanks in advance.

Luigi

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 05:31:52

CASE
WHEN ISNULL(d.DQprod) OR d.DQprod = 0
THEN 0
ELSE
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/ISNULL(d.DQprod,0) * ISNULL(p.PCIMedio,0))/1000000,0)
END

------------------------
PS - Sorry my bad english

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-20 : 05:37:56

With this change:

SELECT @CalorieDaCaloTotale =

CASE WHEN ISNULL(d.DQprod,0)=0 OR ISNULL(p.PCIMedio,0) = 0
THEN 0
ELSE
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/ISNULL(d.DQprod,0) * ISNULL(p.PCIMedio,0))/1000000,0)
END
from @QCali s
INNER JOIN @NumeratoreQprod n on s.Combustibili_ID = n.CombustibileID
INNER JOIN @DenominatoreQprod d on s.Combustibili_ID = d.CombustibileID
INNER JOIN @PCIMedio p on s.Combustibili_ID = p.Combustibili_ID

now I'm having this error:

Column '@DenominatoreQprod.DQprod' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-20 : 05:51:02

CASE
WHEN d.DQprod > 0
THEN
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/d.DQprod * ISNULL(p.PCIMedio,0))/1000000,0)
ELSE
0
END

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 05:53:30

SELECT @CalorieDaCaloTotale =

CASE WHEN ISNULL(SUM(d.DQprod)),0)=0 OR ISNULL(SUM(p.PCIMedio),0) = 0
THEN 0
ELSE
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/ISNULL(d.DQprod,0) * ISNULL(p.PCIMedio,0))/1000000,0)
END
from @QCali s
INNER JOIN @NumeratoreQprod n on s.Combustibili_ID = n.CombustibileID
INNER JOIN @DenominatoreQprod d on s.Combustibili_ID = d.CombustibileID
INNER JOIN @PCIMedio p on s.Combustibili_ID = p.Combustibili_ID

------------------------
PS - Sorry my bad english

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 05:57:10

Not sure what result you are expecting but you are not divide by p.PCIMedio.

see if you don't need this:
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/(ISNULL(d.DQprod,0) * ISNULL(p.PCIMedio,0)))/1000000,0)

------------------------
PS - Sorry my bad english

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-20 : 06:18:35

Here we are, with the correct parenthesis positions:

SELECT @CalorieDaCaloTotale =

CASE WHEN ISNULL(SUM(d.DQprod),0)=0 OR ISNULL(SUM(p.PCIMedio),0) = 0
THEN 0
ELSE
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/ISNULL(d.DQprod,0) * ISNULL(p.PCIMedio,0))/1000000,0)
END
from @QCali s
INNER JOIN @NumeratoreQprod n on s.Combustibili_ID = n.CombustibileID
INNER JOIN @DenominatoreQprod d on s.Combustibili_ID = d.CombustibileID
INNER JOIN @PCIMedio p on s.Combustibili_ID = p.Combustibili_ID

Thank you jleitao and SqlProgrammers.

Luigi

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-20 : 11:42:17

Instead of this:

CASE WHEN ISNULL(SUM(d.DQprod),0)=0 OR ISNULL(SUM(p.PCIMedio),0) = 0
THEN 0
ELSE
ISNULL(sum(ISNULL(s.Somma,0) * ISNULL(n.NQprod,0)/ISNULL(d.DQprod,0) * ISNULL(p.PCIMedio,0))/1000000,0)
END

You can do this:

COALESCE(SUM((s.Somma * n.NQprod) / NULLIF(d.DQprod * p.PCIMedio, 0)) / 1000000 , 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources