Post reply

Divide by zero encountered

  • August 22, 2006 at 6:06 am

    #367179

    Hi,

    I'm having a problem with a view that I've created - I get an error message that says 'Divide by zero encountered' .

    Is there any way that I can set the field so that when it encounters a divide by zero it automaticallly gets changed to Null value?

    thanks

  • August 22, 2006 at 6:45 am

    #656098

    Either protect each divide using a CASE staement

    or

    see SET ARITHIGNORE in BOL (Books Online)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • August 23, 2006 at 9:52 am

    #656408

    You also can use NULLIF(fld, 0) in divider

  • August 24, 2006 at 1:57 am

    #656508

    Why would division by zero be better than a division by NULL?

     

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

  • August 24, 2006 at 5:05 am

    #656527

    NULLIF takes 2 parameters, returns null if the two equal each other, otherwise it returns the first.

    Hence NULLIF(fld, 0) returns NULL if fld is zero, otherwise it returns fld.

    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

  • August 24, 2006 at 9:17 am

    #656620

    Any arithmetic operation with NULL returns NULL.

    SELECT fld1/fld2 will cause an error if fld2 = 0

    SELECT fld1/NULLIF(fld2, 0) return NULL if fld2 = 0

  • September 4, 2006 at 2:58 am

    #658275

    Just got back from holiday to find this reply -  it works - thanks!

     

  • September 11, 2007 at 3:32 pm

    #732924

    Thanks.  This is the first time I've used the site and you bailed me out. 

Viewing 8 posts - 1 through 7 (of 7 total)

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