Preventing division by zero

Hi all

Im looking for a way of preventing a divison by zero in a query. What would be the best way to do it?

The query is the following :

Code Snippet

SELECT

stn.cTblName ,stn.dblGrosseur,(stn.dblgrosseur-sta.dblgrosseur),((stn.dblGrosseur-sta.dblGrosseur)/(sta.dblGrosseur))*100, -- Div / 0 here

stn.intNombre,

(stn.intNombre - sta.intNombre),

((stn.intNombre-sta.intNombre)/(sta.intNombre)) -- div / 0 here too

FROM

vwStatNew stn LEFTOUTERJOIN

vwStatOld sta ON sta.cTblName = stn.cTblName

WHERE

sta.cTblName = stn.cTblName

I would suggest using CASE construct, but a real issue is: what do you want to "force" as a result when you divide by zero?

Maybe starting with something like:

Code Snippet

SELECT
stn.cTblName ,stn.dblGrosseur,(stn.dblgrosseur-sta.dblgrosseur),
case when sta.dblGrosseur <> 0
then ((stn.dblGrosseur-sta.dblGrosseur)/(sta.dblGrosseur))*100
end, -- Div / 0 here
stn.intNombre,
(stn.intNombre - sta.intNombre),
case when sta.intNombre <> 0
then ((stn.intNombre-sta.intNombre)/(sta.intNombre))
end -- div / 0 here too
FROM
vwStatNew stn LEFT OUTER JOIN
vwStatOld sta ON sta.cTblName = stn.cTblName

WHERE
sta.cTblName = stn.cTblName

|||

Use a CASE structure, i.e.,

Code Snippet

SELECT
stn.cTblName,
stn.dblGrosseur,
(stn.dblgrosseur-sta.dblgrosseur),
CASE
WHEN sta.dblGrosseur <> 0 THEN((stn.dblGrosseur-sta.dblGrosseur)/(sta.dblGrosseur))*100, -- Div / 0 here
ELSE NULL --or whatever value you wish if the divisor is zero
END
stn.intNombre,
(stn.intNombre - sta.intNombre),
CASE
WHEN sta.intNombre <> 0 THEN ((stn.intNombre-sta.intNombre)/(sta.intNombre)) -- div / 0 here too
ELSE NULL --or whatever value you wish if the divisor is zero
END
FROM vwStatNew stn
LEFTOUTERJOIN vwStatOld sta
ON sta.cTblName = stn.cTblName
WHERE sta.cTblName = stn.cTblName

|||

Here is a slightly simpler solution that will work if you want the result of the attempt to divide by zero to be null:

SELECT

stn.cTblName ,stn.dblGrosseur,(stn.dblgrosseur-sta.dblgrosseur),((stn.dblGrosseur-sta.dblGrosseur)/(nullif(sta.dblGrosseur,0)))*100,-- Div / 0 here

stn.intNombre,

(stn.intNombre - sta.intNombre),

((stn.intNombre-sta.intNombre)/(nullif(sta.intNombre,0)))-- div / 0 here too

FROM

vwStatNew stn LEFTOUTERJOIN

vwStatOld sta ON sta.cTblName = stn.cTblName

WHERE

sta.cTblName = stn.cTblName

Ron Rice