Divide by zero error encountered

Question

Divide by zero error encountered

ramadesai108

SSChampion

Points: 10609
More actions
November 6, 2008 at 1:21 pm

#75357

Hi All,
Why is the following commented out code is returning
Divide by zero error encountered
but uncommented code is returning the correct result.
If t.SoldYR2 > t.SoldYR1 then t.SoldYR2 can never be zero.
(CASE WHEN t.SoldYR1 > t.SoldYR2 THEN
CAST(ROUND((((t.SoldYR1 - t.SoldYR2)/(t.SoldYR1 * 1)) * 100),0) AS VARCHAR) + '%'
--WHEN t.SoldYR2 > t.SoldYR1 THEN
--CAST(ROUND((((t.SoldYR1 - t.SoldYR2)/
--(t.SoldYR2 * 1)) * 100),0) AS VARCHAR) + '%'
ELSE '0%' END)
Thanks.

Viewing 15 posts - 1 through 15 (of 26 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You're dividing by t.SoldYR1 in the code that works and t.SoldYR2 in the code that doesn't. Are there any rows where t.SoldYR2 is 0?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rosh-396756 SSCrazy Points: 2338 More actions · Answer 2

rosh-396756

SSCrazy

Points: 2338

November 6, 2008 at 9:59 pm

#895309

Would null values cause this.

See if ISNULL helps.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

Use

NULLIF(t.SoldYR2,0)

Madhivanan

Failing to plan is Planning to fail

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 4

Kishore.P

SSCrazy Eights

Points: 8147

November 7, 2008 at 1:11 am

#895347

use COALESCE function instead of IsNull or NullIf.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

CASE

WHEN t.SoldYR1 <> 0 THEN CAST(ROUND(100.0E * (t.SoldYR1 - t.SoldYR2) / t.SoldYR1, 0) AS VARCHAR(11)) + '%'

ELSE '0%'

END

N 56°04'39.16"
E 12°55'05.25"

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 6

Kishore.P (11/7/2008)
use COALESCE function instead of IsNull or NullIf.

Why?

Madhivanan

Failing to plan is Planning to fail

Garadin One Orange Chip Points: 29613 More actions · Answer 7

Kishore.P (11/7/2008)
use COALESCE function instead of IsNull or NullIf.

You can make an argument for using it instead of ISNULL, but NULLIF doesn't do the same thing as coalesce.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ramadesai108 SSChampion Points: 10609 More actions · Answer 8

All,

The commented code is the one that is having divide by zero error:

-- WHEN t.SoldYR2 > t.SoldYR1 THEN

-- CAST(ROUND((((t.SoldYR1 - t.SoldYR2)/

--(t.SoldYR2 * 1)) * 100),0) AS VARCHAR) + '%'

First of all there are no nulls in these fields.

SoldYR1 and SoldYR2 contains sales figures.

My question is: if t.SoldYR2 is greater than t.SoldYR1 (which

means t.SoldYR2 has to be at least greater than zero). If it is greater than zero, then it cannot be zero. So how it is showing as

"divide by zero" error.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

GilaMonster (11/6/2008)
You're dividing by t.SoldYR1 in the code that works and t.SoldYR2 in the code that doesn't. Are there any rows where t.SoldYR2 is 0?

Better question, are there any rows where both t.SoldYR2 and t.SoldYR1 are zero (0)?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

Why can sales be less than zero?

If you have many returns you can. Especially early in the year when every one returns their crappy christmas gifts.

N 56°04'39.16"
E 12°55'05.25"

ramadesai108 SSChampion Points: 10609 More actions · Answer 11

Hi Lynn,

Yes there are rows where both t.SoldYR1 and t.SoldYR2 are zero. But since they are equal they fall through the condition and give me '0%'. There are cases where t.SoldYR1 is zero and t.SoldYR2 is not zero. In that case the following condition would apply:

--WHEN t.SoldYR2 > t.SoldYR1 THEN

--CAST(ROUND((((t.SoldYR1 - t.SoldYR2)/(t.SoldYR2 * 1)) * 100),0) AS VARCHAR) + '%'

There are cases where t.SoldYR2 is zero and t.SoldYR1 is not zero. In that case the following condition would apply, which is working fine:

CASE WHEN t.SoldYR1 > t.SoldYR2 THEN

CAST(ROUND((((t.SoldYR1 - t.SoldYR2)/(t.SoldYR1 * 1)) * 100),0) AS VARCHAR) + '%'

Thanks.

Pac123 SSCrazy Points: 2464 More actions · Answer 12

What if you add a condition -where t.yearsld2<>0 (or whatever) the denominator is. That way you can work aorund having to divide by zero.

What about the datatypes? is it rounding it to zero?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

rosh (11/6/2008)
Would null values cause this.
See if ISNULL helps.

Null won't cause a divide by zero. Anything divided by null (or added to null or multiplied by null, ...) is null. Only a divide by zero causes a divide by zero error

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

ramadesai108 (11/7/2008)
My question is: if t.SoldYR2 is greater than t.SoldYR1 (which
means t.SoldYR2 has to be at least greater than zero). If it is greater than zero, then it cannot be zero. So how it is showing as
"divide by zero" error.

Do you have a check constrain on there to ensure that SoldY1 and SoldY2 are not less than zero? I know it shouldn't happen, but if there's no constraint to prevent it, it may have happened.

What does this return?

SELECT count(*) FROM < Table Name > WHERE SoldYr1 < 0 AND SoldYr2 = 0

What are the datatypes of those columns?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass