Dividing by 0 problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Dividing by 0 problem

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-12-14 : 08:36:43

Hello All,

When I run the below script in SQL Server 200 I get this error:

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
The statement has been terminated.

Does anyone know how to solve this problem?

Thanks.


INSERT INTO FINAL_SKILL
SELECT Test.row_date, Test.split, BASE_GROUP.BASE_GROUP, SUM(Test.acdcalls + Test.abncalls) AS NCO, SUM(Test.acdcalls) AS NCH, 
SUM(Test.abncalls) AS NCA, SUM(Test.i_acdtime + Test.i_acdothertime + Test.i_acdaux_outtime) AS TTT, SUM(Test.acwtime) AS TWT, 
SUM(Test.transferred + Test.conference) AS NTO, SUM(Test.anstime) AS TAS, SUM(Test.acceptable) AS NCH40, 
SUM(Test.acdcalls1 + Test.acdcalls2 + Test.acdcalls3 + Test.acdcalls4) AS NCH20,
(SUM(Test.acceptable)/SUM(Test.acdcalls + Test.abncalls)) AS SVL40, (SUM(Test.acdcalls1 + Test.acdcalls2 + Test.acdcalls3 + Test.acdcalls4)/SUM(Test.acdcalls + Test.abncalls)) AS SVL20
FROM         Test INNER JOIN BASE_GROUP ON Test.split = BASE_GROUP.SKILL
GROUP BY Test.row_date, Test.split, BASE_GROUP.BASE_GROUP
HAVING      (Test.row_date = CONVERT(DATETIME, '12/2/2007', 102))
ORDER BY Test.row_date DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 08:43:39

use case when to check for non 0 before performing division

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 08:46:51

select 10/0

What do you expect?
0,NULL or 10?

Madhivanan

Failing to plan is Planning to fail

pras2007
Posting Yak Master

216 Posts

Posted - 2007-12-14 : 09:15:27

Thanks for the qucik response folks... I check all the filed in question and ther's no null values or 0 values. Could it be the way that I am dividing and summing? Please advise. Thanks.

pras2007
Posting Yak Master

216 Posts

Posted - 2007-12-14 : 10:06:00

madhivanan,

From your example, I will want to see 0 as the value when dividing by 0 (10/0). Thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-12-14 : 10:07:28

Could you have values which make this calculation 0..."Test.acdcalls + Test.abncalls"...(ie a "Test.acdcalls" value of +5) and a "Test.abncalls" value of -5?)

you will still need a CASE clause.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-12-14 : 10:46:31

Theis is a good general solution to preventing divide by zero errors. The result will be null if the divisor is zero.

select
	a.MyQty / nullif(a.MyDivisor,0)
from
	MyTable a

CODO ERGO SUM

MAPEIRIS@hotmail.com
Starting Member

1 Post

Posted - 2007-12-14 : 21:53:54

This should help
declare @1 int ,@2 int
select @1=1,@2=-1

select @1/CASE @2
WHEN 0 THEN 1 ELSE @2
END
select @1=1,@2=0
select @1/CASE @2
WHEN 0 THEN 1 ELSE @2
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources