Divide by 0 in query

Hi, I want to something very simple...

a query that divides two fields and does not crap out when it gets a divide by 0 error.

this is my query...

select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(cache.respondentcount/nodes.inviteecount * 100) as percentage
from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname

PLEASE HELP!!CREATE FUNCTION returnNullIf0 (@.Num Varchar(100))
RETURNS Varchar(100) AS
--if the passed value is 0, the function returns null
--needs to be used whereever the value is used as denominator
BEGIN
DECLARE @.NewNum as varchar(100)
if isnumeric(@.Num) = 1
Begin
IF round(@.Num, 5)= 0
BEGIN
Select @.NewNum = null
END
Else
Select @.NewNum = @.Num
END
ELSE
BEGIN
Select @.NewNum = @.Num
END
Return(@.NewNum)
END

go


select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(returnNullIf0(cache.respondentcount)/returnNullIf0(nodes.inviteecount) * 100) as percentage
from
cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname|||Originally posted by ngillis
Hi, I want to something very simple...

a query that divides two fields and does not crap out when it gets a divide by 0 error.

this is my query...

select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(cache.respondentcount/nodes.inviteecount * 100) as percentage
from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname

PLEASE HELP!!

Also, you could use case:

(cache.respondentcount/case when isnnodes.inviteecount=0
then 1 -- or whatever you want
else isnnodes.inviteecount
end * 100)|||It somewhat depends on what you want to return when the divisor is zero. Zero? Null? A message?

blindman