Error 3628 on a value of -1.#INF in a float field

[Ted Zatopek]

I have a table with a field of datatype float. One record when
viewed through Enterprise Mgr shows a value of "-1.#INF". The same
record does not get returned when doing a SELECT * from the table.
However if I put the field in a WHERE clause I get a message...

Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current
transaction is canceled.

Does anybody know what -1.#INF means?

Does anybody know how I can write an UPDATE statement to change this
value? I'd like to do this through an UPDATE statement so I can
automate the clearing of this value?

Does anybody know what kind of constraint or trigger I can put in
place to keep this value from getting stored?

Any ideas would really help. My boss wants me to make sure the
records get processed correctly, and right now they are not being
processed.

[Steve Kass]

Ted,

In the IEEE 754 standard for the binary representation of floating
point numbers, certain bit patterns represent Infinity, -Infinity and
"not a number", or NaN.

While I've never seen this in EM before, my guess is that this
data came from a source that implemented IEEE 754 fully. SQL
Server does not, and in particular doesn't use +/- Infinity and NaN.
It sounds like the Enterprise Manager at least knows about those
bit patterns and displays -1.#INF for -Infinity, but when that bit
pattern comes up in a calculation (the comparison in your where
condition), it causes a floating point exception.

I'm curious where the data came from - I would think that whatever
routine imported the data shouldn't have let this value through. I'm also
curious as to what you mean when you say "does not get returned" when
you do select *. Do you mean the row containing that value simply doesn't
appear, of that the value appears as NULL?

Unfortunately, there's no easy way to do much testing in the Query
Analyzer, since binary-to-float conversions are disallowed. But I might
see what happens if I write an extended stored procedure to return a
result set containing one of these bit patterns in a float column...

Steve Kass
Drew University

[Steve Kass]

Ted,

I created an extended stored procedure that returns a one-row
result set containing a column of type real with the value -infinity
and reproduced what you see in the Enterprise Manager by inserting
the output of the xp into a table with a real column.

I'm using SQL Server 2000 SP2, and when I do a select * from
the table, I get this error message:

[Microsoft][ODBC SQL Server Driver]Numeric value out of range

I can delete the problem row from the table with this query:

delete from T where isNumeric(r) = 0

I don't know if any of this helps you, but it's interesting...

Steve Kass
Drew University

[Luc]

Can you select the value from QA or OSQL? That should work.
If not let me know which value you see.

Luc

"Ted Zatopek" <tzat...@orixcm.com> wrote in message
news:ff18380f.02041...@posting.google.com...