Divide by zero - 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

General SQL Server Forums

New to SQL Server Programming

Divide by zero

Author

Topic

yuchenchen
Starting Member

4 Posts

Posted - 2009-09-24 : 15:16:04

I would like to know how to revise the following program so that I will not receive the erro message while program is simple and clear. Some of the data, the divisor is zero.

alter table AA add RATE numeric (5,2);
update AA set RATE=(eaten_apple/total_apple);
-->this one will get erro message

What I did was:
alter table alter table AA add RATE numeric (5,2);
update AA set RATE=0 where eaten_apple=0 or total_apple=0;
update AA set RATE=(eaten_apple/total_apple)where EATEN_APPLE<>0 or total_apple<>0;

How should I revise my old version?

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 15:21:48

Here's one way:

update AA set RATE = isNull(eaten_apple/nullif(total_apple,0),0)

Be One with the Optimizer
TG

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2009-09-24 : 15:22:54

SET ARITHABORT OFF;
GO
check the article: http://msdn.microsoft.com/en-us/library/ms190306(SQL.90).aspx

yuchenchen
Starting Member

4 Posts

Posted - 2009-09-24 : 15:31:12

Thanks. I tried that before. I am using Golden Benthic Software(company use), and somehow isNull is an invalid identifier. Does anyone know how to fix it?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-24 : 15:34:37

http://www.benthicsoftware.com/index.htm
Benthic Software produces high quality tools for Oracle databases.

This is a Microsoft SQL Server only site. You should ask your question of a site that answers Oracle questions.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 15:36:54

quote:
Originally posted by Dance Doll

SET ARITHABORT OFF;
GO
check the article: http://msdn.microsoft.com/en-us/library/ms190306(SQL.90).aspx

You also need to have set ansi_warnings set to OFF to avoid the devide by zero error. But the user specified that they want 0 instead of null and setting those to OFF will result in NULL.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources