How to Avoid Divide By Zero Error

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 Avoid Divide By Zero Error

Author

Topic

ladowali
Starting Member

16 Posts

Posted - 2013-11-06 : 14:43:56

Hi Group,

I have some SQL scripts for SQL Views and getting divide by zero error. I am using COALESCE(Field1,Field2,1)/COALESCE(Field3,Field4,1)

This function can avoid NULL but how I can avoid 0 ?

Is there any function in SQL that can take care both NULL and 0 and replace it with 1.

Thanks
Lado

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 14:55:58

NULLIF(Field1, 0)

This will change Field1 to NULL if the value is 0. So:

COALESCE(Field1,Field2,1)/NullIF(COALESCE(Field3,Field4,1), 0)

EDIT:
Don't forget to search the site before posting a new question. "Devide by zero" is a common problem that has been answered countless times here.

Be One with the Optimizer
TG

ladowali
Starting Member

16 Posts

Posted - 2013-11-06 : 15:22:19

quote:
Originally posted by TG

NULLIF(Field1, 0)

This will change Field1 to NULL if the value is 0. So:

COALESCE(Field1,Field2,1)/NullIF(COALESCE(Field3,Field4,1), 0)

EDIT:
Don't forget to search the site before posting a new question. "Devide by zero" is a common problem that has been answered countless times here.

Be One with the Optimizer
TG

Thanks TG, I will keep this in mind. Anyway I learned few new things today and hope to learn more in the coming time through this forum.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 15:24:47

You're welcome. And I didn't mean to make that sound like a scold :) Keep using the forum AND posting questions.

Be One with the Optimizer
TG

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-06 : 15:45:21

=>Is there any function in SQL that can take care both NULL and 0 and replace it with 1.<=

--Way #1
ISNULL(NULLIF(COALESCE(Field1,Field2,1), 0), 1)/ISNULL(NULLIF(COALESCE(Field3,Field4,1), 0), 1)

--Way #2
IIF(Field1 > 0, Field1, IIF(Field2 > 0, Field2, 1)) / IIF(Field3 > 0, Field3, IIF(Field4 > 0, Field4, 1))

ladowali
Starting Member

16 Posts

Posted - 2013-11-06 : 16:16:37

quote:
Originally posted by sigmas

=>Is there any function in SQL that can take care both NULL and 0 and replace it with 1.<=

--Way #1
ISNULL(NULLIF(COALESCE(Field1,Field2,1), 0), 1)/ISNULL(NULLIF(COALESCE(Field3,Field4,1), 0), 1)

--Way #2
IIF(Field1 > 0, Field1, IIF(Field2 > 0, Field2, 1)) / IIF(Field3 > 0, Field3, IIF(Field4 > 0, Field4, 1))

Thanks Sigma

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources