Divide by Zero error 1

[firsttube]

I am getting an error in a view:

[Microsoft][ODBC SQL Server Driver][SQL Server]Divide by zero error encountered

It is caused by a calculation I have that divides one column by another to make a third column. some of the values in the "divide by" field are zero. This never used to cause an error until just today....strange.

The calculation is as follows:
CAST(ROUND(db.FIELD1.UNITS / (db.TABLE2.FIELD2 * 0.0001), 2) AS decimal(10, 2)) AS DENSITY

Any suggestions as to how to avoid this error, while still keeping the zeros in the field?

thanks

ft

 

[jelf]

If you turn off ARITHABORT and ANSI_WARNINGS then divide by zero returns a NULL.

Is this what you want?

 

[firsttube]

Thanks! That sounds like it is the problem. But, where can I change the ARITHABORT and ANSI_WARNINGS settings? i am using the SQL Server Enterprise Manager.

thanks for your help...

ft

 

[jelf]

If you are using Query Analyzer you can use:

set arithabort off
set ansi_warnings off

In EM goto Tools -> SQL Configuration -> Connection Properties.

 

[firsttube]

Ok. I checked both the ARITHABORT and ANSI_WARNINGS settings and they are both set to off. The error is still occurring.

Thanks for your help with this.

ft

 

[JayKusch]

Maybe the field is not Zero but NULL??? Try this calc:

CAST(ROUND(ISNULL(db.FIELD1.UNITS,0) / (ISNULL(db.TABLE2.FIELD2,0) * 0.0001), 2) AS decimal(10, 2)) AS DENSITY



Thanks

J. Kusch

 

[firsttube]

No, the field does have zero's in it. I developed a work-around that works well in the Query Analyzer:

CASE WHEN db.Table1.AREA = 0 THEN 0 ELSE CAST(ROUND(db.Table2.UNITS / (db.Table1.AREA * 0.0001), 2) AS decimal(10, 2)) END
AS DENSITY

But when I paste this SQL into the Query Designer for a new view in Enterprise Manager, I get an error saying that Query Designer does not support that CASE sql construct.

How can I replicate that SQL in the View in Query Designer.

thanks

ft

 

[ctarr]

ft, you'll be able to run the sql if you put it into a stored procedure and execute that from the query designer to test.

Build your basic select syntax and then move to the stored procedure view to add in the case syntax.

The designer cannot build a case statement, it's too complex for the program to display. It's really just a limitation of the software.

Craig