Post reply

Computed Columns & Divide By Zero Errors

  • January 14, 2013 at 6:00 am

    #269192

    Hi,

    I have some computed columns in a table that under some circumstances fail due to a divide by zero error. Now, I know I can just use regular columns and do the required calculations with appropriate logic to avoid any divide by zero errors and achieve the result I want. But, is there a way of maintaining the computed columns and avoid such errors?

    Regards

    Steve

  • January 14, 2013 at 6:15 am

    #1576854

    For clarity, can you share your example schema definition, along with test data indicating the desired output please?

    One suggestion would be to handle the case for 0 within your computed column definition and assigning NULL in it's place.

    create table testTable

    (

    ValueOne int,

    ValueTwo int,

    Calcuation as ValueOne/case when ValueTwo = 0 then null else ValueTwo end

    )

    insert into testTable(ValueOne,ValueTwo) values(1,1)

    insert into testTable(ValueOne,ValueTwo) values(1,0)

    select * from testTable

    John Sansom (@sqlBrit) | www.johnsansom.com

  • January 14, 2013 at 6:22 am

    #1576855

    So the computed column is ColA / ColB, what do you want the output to be.

    If ColA is 10 and ColB is 0, do you want the computation to be 10 or 0

    For 0

    ISNULL(ColA / NULLIF(ColB, 0), 0)

    For 10

    ColA / ISNULL(NULLIF(ColB,0), 1)

  • January 14, 2013 at 7:34 am

    #1576894

    Looks as if your examples provide the answers I need.

    Thanks 🙂

  • January 14, 2013 at 7:38 am

    #1576898

    raotor (1/14/2013)

    Looks as if your examples provide the answers I need.

    Thanks 🙂

    You're welcome, glad we could help.

    John Sansom (@sqlBrit) | www.johnsansom.com

Viewing 5 posts - 1 through 4 (of 4 total)

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