divide by zero error encountered

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)

divide by zero error encountered

Author

Topic

nomvula
Starting Member

3 Posts

Posted - 2006-08-10 : 05:41:33

hi
i need help in running my query, i'm getting an error, see subject above and i understand that i have to use the nullif function but how do i use it in the following query: ((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL)) *100,1) as [%Failed Rate]

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-10 : 05:56:46

((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL)) *100,1) as [%Failed Rate]

If you have 0 rows that meet the criteria: FROM DC_Forms WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL
then count(1) will resolve to null

Try:


((	SELECT (COUNT(1))
	FROM DC_Forms
	WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
COALESCE(  --this function will replace a NULL with further specified values. read bol for more.
	((SELECT (COUNT(1))
	FROM DC_Forms
	WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL))
,1) --further speccifed value. decide what you need here
*100,1)
as [%Failed Rate]

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 05:57:33

[code]select ((SELECT COUNT(1)
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
((SELECT case COUNT(1) when 0 then 1 else COUNT(1) end
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL) *100)) as [%Failed Rate][/code]

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 06:00:04

quote:
Originally posted by Wanderer

((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL)) *100,1) as [%Failed Rate]

If you have 0 rows that meet the criteria: FROM DC_Forms WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL
then count(1) will resolve to null

Try:

(( SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
COALESCE( --this function will replace a NULL with further specified values. read bol for more.
((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL))
,1) --further speccifed value. decide what you need here
*100,1)
as [%Failed Rate]

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Even if no rows matching the given crieteria are found, count(1) will not return NULL, it will return zero.

Select count(1) from SomeTable where 1=0

Harsh Athalye
India.
"Nothing is Impossible"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-10 : 06:05:51

quote:
Originally posted by harsh_athalye

quote:
Originally posted by Wanderer

((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL)) *100,1) as [%Failed Rate]

If you have 0 rows that meet the criteria: FROM DC_Forms WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL
then count(1) will resolve to null

Try:

(( SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) /
COALESCE( --this function will replace a NULL with further specified values. read bol for more.
((SELECT (COUNT(1))
FROM DC_Forms
WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL))
,1) --further speccifed value. decide what you need here
*100,1)
as [%Failed Rate]

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Even if no rows matching the given crieteria are found, count(1) will not return NULL, it will return zero.

Select count(1) from SomeTable where 1=0

Harsh Athalye
India.
"Nothing is Impossible"

Thanks Harsh - you're of course correct, since it is a function - I was being a bit blonde.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

nomvula
Starting Member

3 Posts

Posted - 2006-08-10 : 07:51:52

thank you so much guys, that was prompt... well i used the case function and it didn't give me that error now but it seems like all the values are assigned to 0. i also tried to use the coalesce function but can you try and elaborate that one to me cause i don't understand what's happening

thanks again in advance

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-10 : 08:19:38

Hi nomvula,
I should note that the coalesce will NOT be useful since the 0 <> null. So scrap my idea.

When you run the individual sections of that query, what do you get? As Harsh pointed out, you are likely getting 0 for the SQL:


SELECT (COUNT(1))
	FROM DC_Forms
	WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL

and possible you are getting 0 for this 'top' bit as well:


SELECT (COUNT(1))
FROM DC_Forms WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null

so your original SQL was resolving to ((0)/(0)), which gave you the divide by 0 error.

Harsh's suggestion basically replaces the 0 in you divider, so you ended up with ((0)/(1)), which = 0.

Review your base SQL statements, and your data. I would recommend, just while debugging, you run something like to review the values:


select 
	(SELECT COUNT(1) FROM DC_Forms
	WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) AS AboveTheLine,
	(SELECT case COUNT(1) when 0 then 1 else COUNT(1) end FROM DC_Forms
	WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL) AS BelowTheLine,
	((SELECT COUNT(1) FROM DC_Forms
	WHERE PrintBatchID = DC_Batches.PrintBatchID and ProcessedOn is not null) / 
	((SELECT case COUNT(1) when 0 then 1 else COUNT(1) end FROM DC_Forms
	WHERE PrintBatchID = DC_Batches.PrintBatchID AND edit_end IS NOT NULL) *100)) as [%Failed Rate]

ps: Groete uit engeland van nog 'n Suid-Afrikaner in Engeland
*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources