none
Divide by 0 error RRS feed

  • Question

  • I have created the following query but if the cer allowance is null or 0 get an error as it can not divide by 0 is there any way I can stop this error

    SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
    FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
    WHERE compliance.Company_ID = 890 and compliance.year1 = '2008'
    GROUP BY compliance.Company_ID, compliance.year1
    Saturday, February 7, 2009 3:54 PM

Answers

  • This is more likely the cause of the error:

       a.cer/b.SurrenderedCers

    You can prevent the error by substituting a NULL value for a zero value, something like:

       a.Cer / nullif( b.SurrenderedCers, 0 )


    You may be only one person in the world, but you may also be the world to one person.
    • Marked as answer by ateh Saturday, February 7, 2009 4:54 PM
    Saturday, February 7, 2009 4:36 PM
    Moderator

All replies

  • In the query you posted, you will not get a divide by zero error -since you are dividing cer.CerAllowance by 100.
    (That is allowed, even if cer.CerAllowance is zero or NULL.)

    (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0))


    You may be only one person in the world, but you may also be the world to one person.
    Saturday, February 7, 2009 4:24 PM
    Moderator
  • That part of the query is just 1 half of the query, which I thought was giving me the error the full query is below which is the query I get a divide error on

    SELECT  a.company_id, a.cer/b.SurrenderedCers AS MyDivision 
    FROM    (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1 
            FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier 
            WHERE compliance.Company_ID = 1 and compliance.year1 = '2008'
            GROUP BY compliance.Company_ID, compliance.year1) AS a 
    JOIN    (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs 
            FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier 
            WHERE compliance.Company_ID = 1 
            GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id 
    Saturday, February 7, 2009 4:28 PM
  • This is more likely the cause of the error:

       a.cer/b.SurrenderedCers

    You can prevent the error by substituting a NULL value for a zero value, something like:

       a.Cer / nullif( b.SurrenderedCers, 0 )


    You may be only one person in the world, but you may also be the world to one person.
    • Marked as answer by ateh Saturday, February 7, 2009 4:54 PM
    Saturday, February 7, 2009 4:36 PM
    Moderator