Divide by zero error - SQL Server Forums

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

Transact-SQL (2005)

Divide by zero error

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-02-15 : 09:35:16

Hi,

I have this query

SELECT * FROM tblnewmatrixfigures
WHERE (pch / ch = 1.15)
AND vehicleref IN
(SELECT vehicleref FROM tblnewmatrixstd UNION
SELECT vehicleref FROM tblnewmatrixltd UNION
SELECT vehicleref FROM tblnewmatrixspecial)

It fails with "divide by zero exception encountered"

The reason is that in some cases ch = 0

I'd like to just avoid rows where this is the case so I tried

WHERE (ch = 0) AND (pch / ch = 1.15)

This still failed though. Whats the best way to do this.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-15 : 09:41:13

[code]
SELECT *
FROM (
select *
from tblnewmatrixfigures
where ch <> 0
) a
WHERE (pch / ch = 1.15)
AND vehicleref IN
(
SELECT vehicleref FROM tblnewmatrixstd UNION
SELECT vehicleref FROM tblnewmatrixltd UNION
SELECT vehicleref FROM tblnewmatrixspecial
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 09:45:33

or just make where condition

(pch / NULLIF(ch,0) = 1.15)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 09:52:01

" This still failed though."

The query plan has to be constructed for "generic" case - so it cannot know whether any data will be zero, or any other value, when the actual query data is passed to the Query Plan.

This is different to procedural languages where

IF (FALSE1 AND DivideZero2)

will short-circuit the logic test when it deduces that the expression FALSE1 is False, and won't evaluate DivideZero2 at all. That is NOT the case with SQL Server Query Plans

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-02-15 : 10:22:50

Thanks Kristen, that makes sense :)

And thanks visakh and khtan, both solutions worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 10:33:16

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-15 : 11:32:50

Or you could just use NULLIF:

SELECT * FROM tblnewmatrixfigures
WHERE (pch / NULLIF(ch, = 0.0) = 1.15)
AND vehicleref IN
(SELECT vehicleref FROM tblnewmatrixstd UNION
SELECT vehicleref FROM tblnewmatrixltd UNION
SELECT vehicleref FROM tblnewmatrixspecial)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 11:40:10

quote:

Originally posted by Lamprey

Or you could just use NULLIF:

SELECT * FROM tblnewmatrixfigures
WHERE (pch / NULLIF(ch, = 0.0) = 1.15)
AND vehicleref IN
(SELECT vehicleref FROM tblnewmatrixstd UNION
SELECT vehicleref FROM tblnewmatrixltd UNION
SELECT vehicleref FROM tblnewmatrixspecial)

This is what I suggested earlier at 02/15/2010 : 09:45:33

Also you dont need =

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-15 : 12:49:37

quote:
Originally posted by visakh16

quote:
Originally posted by Lamprey

Or you could just use NULLIF:
SELECT * FROM tblnewmatrixfigures
WHERE (pch / NULLIF(ch, = 0.0) = 1.15)
AND vehicleref IN
(SELECT vehicleref FROM tblnewmatrixstd UNION
SELECT vehicleref FROM tblnewmatrixltd UNION
SELECT vehicleref FROM tblnewmatrixspecial)

This is what I suggested earlier at 02/15/2010 : 09:45:33
Also you dont need =

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for the fix. :)

Wierd, that I only saw Khtan's response.. at anyrate go NULLIF!! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:50:48

quote:
Originally posted by Lamprey

quote:
Originally posted by visakh16

quote:
Originally posted by Lamprey

Or you could just use NULLIF:
SELECT * FROM tblnewmatrixfigures
WHERE (pch / NULLIF(ch, = 0.0) = 1.15)
AND vehicleref IN
(SELECT vehicleref FROM tblnewmatrixstd UNION
SELECT vehicleref FROM tblnewmatrixltd UNION
SELECT vehicleref FROM tblnewmatrixspecial)

This is what I suggested earlier at 02/15/2010 : 09:45:33
Also you dont need =

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for the fix. :)

Wierd, that I only saw Khtan's response.. at anyrate go NULLIF!! :)

No probs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources