Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Computed Columns & Divide By Zero Errors Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, January 14, 2013 6:00 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 23, 2013 7:03 AM Points: 158, Visits: 239
 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?RegardsSteve
Post #1406678
 Posted Monday, January 14, 2013 6:15 AM
 Old Hand Group: General Forum Members Last Login: Monday, February 16, 2015 4:17 AM Points: 351, Visits: 1,558
 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
Post #1406683
 Posted Monday, January 14, 2013 6:22 AM
 SSCertifiable Group: General Forum Members Last Login: Thursday, September 1, 2016 2:56 AM Points: 5,969, Visits: 6,067
 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 0For 0ISNULL(ColA / NULLIF(ColB, 0), 0)For 10ColA / ISNULL(NULLIF(ColB,0), 1)
Post #1406684
 Posted Monday, January 14, 2013 7:34 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 23, 2013 7:03 AM Points: 158, Visits: 239