SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Avoid Divide By Zero Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ladowali
Starting Member

Canada
11 Posts

Posted - 11/06/2013 :  14:43:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 11/06/2013 :  14:55:58  Show Profile  Reply with Quote
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

Edited by - TG on 11/06/2013 14:58:05
Go to Top of Page

ladowali
Starting Member

Canada
11 Posts

Posted - 11/06/2013 :  15:22:19  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 11/06/2013 :  15:24:47  Show Profile  Reply with Quote
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
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 11/06/2013 :  15:45:21  Show Profile  Reply with Quote
=>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))

Go to Top of Page

ladowali
Starting Member

Canada
11 Posts

Posted - 11/06/2013 :  16:16:37  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000