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

prevent zero length entries

I have a couple critical fields where I am setting them to no nulls but just
realized that they can be set to zero length strings very easily. Other than
a trigger or handling the validation in the front end, is there an easy way
to handle this?
MS Access is my front end so interestingly enough, if you select an the
entire text in a field and hit the delete key it trys to set to NULL not ""
so it's ok. But in EM you can just hit the delete key and it goes to ""
instead of NULL. Obviously end users are goign to use the front end and not
EM so this isn't really a problem but I was just wondering about other front
ends that might not behave the same way. They might set to "" on hitting the
delete key.
THanks,
KeithKeith G Hicks wrote:
> I have a couple critical fields where I am setting them to no nulls but ju
st
> realized that they can be set to zero length strings very easily. Other th
an
> a trigger or handling the validation in the front end, is there an easy wa
y
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not "
"
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and no
t
> EM so this isn't really a problem but I was just wondering about other fro
nt
> ends that might not behave the same way. They might set to "" on hitting t
he
> delete key.
> THanks,
> Keith
>
A check constraint of LEN(ColumnName) > 0 would do it, or you could work
in a more complex requirement. Honestly, if you're worried about people
just putting in an empty string, and you require at least one char,
they'll learn that if they put in 1 char they can save the data. If you
bump the requirement up to 2, they'll put in 2. :D
Zach|||keith,
create a check constraint on table, with expression: datalength(col)>0
hth,
dean
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23w4ZvbwCFHA.1932@.TK2MSFTNGP14.phx.gbl...
> I have a couple critical fields where I am setting them to no nulls but
just
> realized that they can be set to zero length strings very easily. Other
than
> a trigger or handling the validation in the front end, is there an easy
way
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not
""
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and
not
> EM so this isn't really a problem but I was just wondering about other
front
> ends that might not behave the same way. They might set to "" on hitting
the
> delete key.
> THanks,
> Keith
>|||Thank you to both of you. That works just fine of course.
I feel sort of silly now. :)|||"Zach Wells" wrote:
<snip>

> Honestly, if you're worried about people just putting in
> an empty string, and you require at least one char, they'll
> learn that if they put in 1 char they can save the data. If
> you bump the requirement up to 2, they'll put in 2. :D
LOL! Never underestimate the ability of users to circumvent your data
validation routines. We had a situation just like that. You could even map
out the evolution of the validation routine and the users' innovation
(especially since the application had no way of exactly determining what a
correct string was for that particular column). The progression looked
something like:
.
NA
N/A
XXXX
DONTKNOW
:)
Craig