divide by zero error on computed column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

divide by zero error on computed column

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-12-08 : 11:52:41

Hi. I'm altering a table with a computed column of data type float. all cols I'm using to create the computed column are also data type float. Here's my alter table:

ALTER TABLE generic_final

ADD generic11Expected AS generic11totbits * (generic11obsALL + generic12obsALL + generic22obsALL)/(generic11totbits + generic12totbits + generic22totbits)

SQL server is throwing:

SQL odbc driver error, divide by zero error encountered

when it comes across instances of division by zero.

Is there a workaround for this? I'm running sql server v. 7.0 sp4

thx.

mashimaro
Starting Member

3 Posts

Posted - 2003-12-08 : 12:10:26

you should add if-then statement to check if the denomiator is zero or not before executing the statement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-08 : 13:32:58

Instead of IF-THEN logic, you can use CASE statements in your code.

USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

Just change the code around to fit your needs.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-08 : 13:34:54

Your formula should be something like this:

generic11Expected AS
CASE
WHEN (generic11totbits + generic12totbits + generic22totbits) <> 0
THEN
generic11totbits * (generic11obsALL + generic12obsALL +
generic22obsALL)/(generic11totbits + generic12totbits +
generic22totbits)
ELSE
Null
END

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources