Divide by zero error encountered

Question

Post reply

Divide by zero error encountered

Viewing 12 posts - 16 through 26 (of 26 total)

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

ramadesai108 SSChampion Points: 10609 More actions · Answer 1

All,

There are nothing less than zeros in SoldYR1 or SoldYR2. Datatype is Money for both.

Thank you.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Please run these 2 queries:

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

and

SELECT CASE WHEN t.SoldYR1 > t.SoldYR2

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

ELSE ''

END

FROM < Table Name >

And tell us what each of them returns.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 3

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

Why? NullIf is nothing like IsNull or Coalesce and the idea of code portability is a myth.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

GilaMonster (11/8/2008)
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

Actually Gail what I meant was would the if conditions fail because of null values....But I guess that also now is irrelevant.

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

rosh (11/9/2008)
GilaMonster (11/8/2008)
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
Actually Gail what I meant was would the if conditions fail because of null values....But I guess that also now is irrelevant.

They could, but if they did, neither of the case branches would be taken as the expression would evaluate to null and null is not true

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

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 6

What if SoldYR1 is 0 and SoldYR2 is a negative value?

SoldYR1 = 0

SoldYR2 = -1

Thus:

(t.SoldYR1 - t.SoldYR2)/(t.SoldYR1 * 1)

becomes:

(0-(-1))/(0 * 1)

Divide by zero.

Random Technical Stuff[/url]

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 7

Lynn Pettis (11/7/2008)
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)?

I think the case when statement would evaluate to false as that's a greater than condition.

Random Technical Stuff[/url]

ramadesai108 SSChampion Points: 10609 More actions · Answer 8

Hi All,

Sorry it took me longer to reply. Rounding function using "0" or "2" makes the difference. That caused the number to zero out.

Thanks.

ramadesai108 SSChampion Points: 10609 More actions · Answer 9

Hello All,

Now the following statement works fine but it produces number of digits to the right of the decimal:

CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS VARCHAR) + '%'

Since the above produces number of digits to the right of the decimal, I want to truncate it to only 2 digits to the right of the decimal. So I am trying to convert the result to decimal, but the following gives me divide by zero error:

CAST(CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS DECIMAL(9,0)) AS VARCHAR) + '%'

Thanks.

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 10

ramadesai108 (11/14/2008)
Hello All,
Now the following statement works fine but it produces number of digits to the right of the decimal:
CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS VARCHAR) + '%'
Since the above produces number of digits to the right of the decimal, I want to truncate it to only 2 digits to the right of the decimal. So I am trying to convert the result to decimal, but the following gives me divide by zero error:
CAST(CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS DECIMAL(9,0)) AS VARCHAR) + '%'
Thanks.

Could you clarify how the round function zeros out any of the variables? I'm confused because the calculation is done within the round function, and I would have thought that the rounding is done after the calculation is done.

My original comments still stand. Can you have negative values for ItmRecvdYR2? If so, then you can include a negative number for ItmRecvdYR2 and 0 for the number in ItmRecvdYR1 and thus you will get a divide by zero exception.

Random Technical Stuff[/url]

ramadesai108 SSChampion Points: 10609 More actions · Answer 11

Hi ta.bu.shi.ya.du,

In the following:

WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN

CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR2 * 1.0)) * 100),0) AS VARCHAR) + '%'

if I change the rounding to 2 as follows, then I get divide by zero:

WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN

CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR2 * 1.0)) * 100),2) AS VARCHAR) + '%'

There are no zeros in any of the ItmRecvdYR1 or ItmRecvdYR2.

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

ramadesai108, you don't make any sense, because the rounding happens AFTER dividing.

You probably need to expose whole statement to let people find where is your real problem.

Also you could find which data rows cause the error by applying some WHERE conditions.

Then you'd understand better what's really going on.

_____________
Code for TallyGenerator