Divide by zero error encountered

  • 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.

  • 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
  • Would null values cause this.

    See if ISNULL helps.

  • Use

    NULLIF(t.SoldYR2,0)


    Madhivanan

    Failing to plan is Planning to fail

  • use COALESCE function instead of IsNull or NullIf.

  • 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"

  • Kishore.P (11/7/2008)


    use COALESCE function instead of IsNull or NullIf.

    Why?


    Madhivanan

    Failing to plan is Planning to fail

  • 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]

  • 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.

  • 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)?

  • 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"

  • 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.

  • 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?

  • 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
  • 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

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

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