Message: Divisor is equal to zero

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

Message: Divisor is equal to zero

Author

Topic

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 01:55:46

Hi

I'm getting 'Divisor is equal to zero' for some rows. Is there a way in sql to have the result come out as zero in such a case?
i.e., if the divisor 'is' zero, then instead of 'infinity', can the result be converted to '0'?

Thanks.

Regards
Sheraz

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-29 : 02:02:04

You can use NULLIF function in such cases


DECLARE @T TABLE
(
num INT,
denom INT
)

INSERT INTO @T SELECT
10,2 UNION ALL SELECT
5,0 UNION ALL SELECT
4,2 UNION ALL SELECT
3,0

SELECT ISNULL(num/NULLIF(denom,0),0)
FROM @T

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-29 : 02:37:01

You can make use of Case too

Select case when Col2= 0 then 0 else col1/Col2 end as result from mytable

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 02:46:42

Thanks...How can I fit this in my existing query:

select Col1, Col2, Col3, SUM(Col4), ROUND(SUM(Col5)/900,2)
from schema1.table1
where Col1=ABC

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-29 : 02:50:57

quote:
Originally posted by SACK

Thanks...How can I fit this in my existing query:

select Col1, Col2, Col3, SUM(Col4), ROUND(SUM(Col5)/900,2)
from schema1.table1
where Col1=ABC

Here Your divisor 900 is Constant then how u got divide by Zero Error!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-29 : 02:51:37

On which 2 columns you are performing Divison?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 06:09:30

Thanks to everyone. The example I gave here should not have included 900. My mistake. But anyway, using the CASE statement has done the trick. Thanks once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 11:27:32

quote:
Originally posted by SACK

Thanks to everyone. The example I gave here should not have included 900. My mistake. But anyway, using the CASE statement has done the trick. Thanks once again.

NULLIF method is much shorter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 13:37:09

Maybe NULLIF doesn't exist in ORACLE?
I don't know why SACK continues to ask here at SQLTeam for questions regarding his ORACLE queries.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 13:38:29

quote:
Originally posted by Peso

Maybe NULLIF doesn't exist in ORACLE?
I don't know why SACK continues to ask here at SQLTeam for questions regarding his ORACLE queries.

N 56°04'39.26"
E 12°55'05.63"

wow Is he using ORACLE?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 13:53:17

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128395

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources