November 6, 2008 at 1:21 pm
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.
November 6, 2008 at 2:01 pm
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
November 6, 2008 at 9:59 pm
Would null values cause this.
See if ISNULL helps.
November 7, 2008 at 12:53 am
Use
NULLIF(t.SoldYR2,0)
Failing to plan is Planning to fail
November 7, 2008 at 1:11 am
use COALESCE function instead of IsNull or NullIf.
November 7, 2008 at 1:35 am
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"
November 7, 2008 at 3:42 am
Kishore.P (11/7/2008)
use COALESCE function instead of IsNull or NullIf.
Why?
Failing to plan is Planning to fail
November 7, 2008 at 5:56 am
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.
November 7, 2008 at 8:41 am
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.
November 7, 2008 at 8:52 am
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)?
November 7, 2008 at 8:53 am
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"
November 7, 2008 at 9:04 am
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.
November 7, 2008 at 9:18 am
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?
November 8, 2008 at 3:24 am
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
November 8, 2008 at 3:34 am
ramadesai108 (11/7/2008)
My question is: if t.SoldYR2 is greater than t.SoldYR1 (whichmeans 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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply