Divide by Zero

Question

Post reply

Divide by Zero

bjaricha

SSC Enthusiast

Points: 153
More actions
April 11, 2016 at 5:30 am

#309786

Hi All
I have the following script
IF @thirddatediff > 0
BEGIN
Set @lastavgodo = (isNULL(@secondodo,1) - isNULL(@thirdodo,1))/(isNULL(@thirddatediff,1) - isNULL(@seconddatediff,1))
END
IF @seconddatediff > 0
BEGIN
Set @firstavgodo = (isNULL(@firstodo,1) - isNULL(@secondodo,1))/(isNULL(@seconddatediff,1) - isNULL(@firstdatediff,1))
END
Set @odomin = isNULL(@firstodo,0) + avg(isNULL(@firstavgodo,0) + isNULL(@lastavgodo,0) + isNULL(@minvalue,0))
Set @odomax = isNULL(@firstodo,0) + avg(isNULL(@firstavgodo,0) + isNULL(@lastavgodo,0) + isNULL(@maxvalue,0))
when i try to run it i am getting this error (Divide by zero error encountered)
Need your help thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

shaneoneillis SSCommitted Points: 1750 More actions · Answer 1

Hi bjaricha,

You may want to let us know how you got your variables as it could help with the answers to this.

Also with this

Need your help thanks

do you need help with where it could possibly be coming from? Or how to account for it?

If it's where is the error coming from here's a possible hint, what happens in this situation?

DECLARE

@seconddatediff INT = NULL

, @thirddatediff INT = NULL;

SELECT isNULL(@thirddatediff,1) - isNULL(@seconddatediff,1);

-- Especially what happens here?

SELECT 1 / (isNULL(@thirddatediff,1) - isNULL(@seconddatediff,1));

As for how to account for it, well there are smarter people here than myself but I'd say you'd need to provide more information before we can help you there.

djj SSCoach Points: 18831 More actions · Answer 2

You should handle the nulls before you get to the equations.

This does two things.

1) you can read the equations

2) you can handle problems like @thirddatediff = @seconddatediff which will lead to divide by zero

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

shaneoneillis (4/11/2016)
Hi bjaricha,
You may want to let us know how you got your variables as it could help with the answers to this.
Also with this
Need your help thanks
do you need help with where it could possibly be coming from? Or how to account for it?
If it's where is the error coming from here's a possible hint, what happens in this situation?
DECLARE
@seconddatediff INT = NULL
, @thirddatediff INT = NULL;
SELECT isNULL(@thirddatediff,1) - isNULL(@seconddatediff,1);
-- Especially what happens here?
SELECT 1 / (isNULL(@thirddatediff,1) - isNULL(@seconddatediff,1));
As for how to account for it, well there are smarter people here than myself but I'd say you'd need to provide more information before we can help you there.

You hit the nail.

When there's a divide by zero error, we have to go and check any division that can have a divisor which value is zero.

My usual way to avoid it is by using the NULLIF function which would allow us to turn that zero into a NULL value which would give a NULL result. This can be later changed if needed.

SELECT 1 / NULLIF( ISNULL(@thirddatediff,1) - ISNULL(@seconddatediff,1), 0);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

bjaricha SSC Enthusiast Points: 153 More actions · Answer 4

thanks very much, will try and let you know the outcome

bjaricha SSC Enthusiast Points: 153 More actions · Answer 5

Thanks Guys for your help, this one worked (SELECT 1 / NULLIF( ISNULL(@thirddatediff,1) - ISNULL(@seconddatediff,1), 0);)