Removing zeros to stop divide by zero errors

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

SQL Server Development (2000)

Removing zeros to stop divide by zero errors

Author

Topic

Munchausen
Starting Member

25 Posts

Posted - 2005-09-08 : 11:10:40

We have a calculation in a query like this Col1 + Col2 + Col3 / Col4. Unfortunately, we've found that it's possible for Col4 to have a value of zero.

Is there a way to replace zero with one in this instance?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 11:12:10

Try this

Col1 + Col2 + Col3 / (case when Col4=0 then 1 else Col4 end)

Madhivanan

Failing to plan is Planning to fail

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-08 : 11:18:09

won't that be a bogus result in the case of 0?

I think this would be touch more correct even though divide by 0 kicks an error for a reason.

(case when Col4 = 0 then 0 else Col1 + Col2 + Col3 /Col4 end)

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

Munchausen
Starting Member

25 Posts

Posted - 2005-09-08 : 11:28:03

Thanks, guys. Works much better now. :)

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 11:55:59

Is NULL a more acceptable answer when Col4 is zero?

Col1 + Col2 + Col3 / NullIf(Col4, 0)


DECLARE @intValue int
SELECT @intValue = 0
WHILE @intValue <= 1
BEGIN
	SELECT [@intValue] = @intValue,
		[Calc] = 1 / NullIf(@intValue, 0)
	
	SELECT @intValue = @intValue + 1
END

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources