How can I can rid of rows that have -1.#IND

I have several rows that got imported into floats with the -1.#IND value. I
want to run a query to find out which rows it is but I always get a divide
by zero error.
I tried select * from mytable where mycolumn/1 = 0Try:
DELETE
FROM YourTable
WHERE YourFloatCol < CAST(-9.9999999999999998E+37 AS FLOAT)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:%23yhdsoDRFHA.2384@.tk2msftngp13.phx.gbl...
> I have several rows that got imported into floats with the -1.#IND value.
I
> want to run a query to find out which rows it is but I always get a divide
> by zero error.
> I tried select * from mytable where mycolumn/1 = 0
>
>|||If I try:
select cast('-1.#IND' as float)
I get:
Server: Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to float.
What is the value that you want to use to filter the rows?
AMB
"Joe" wrote:

> I have several rows that got imported into floats with the -1.#IND value.
I
> want to run a query to find out which rows it is but I always get a divide
> by zero error.
> I tried select * from mytable where mycolumn/1 = 0
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:386B64F9-2E3C-4D90-BC38-E2DB3BA62478@.microsoft.com...
> If I try:
> select cast('-1.#IND' as float)
> I get:
> Server: Msg 8114, Level 16, State 5, Line 2
> Error converting data type varchar to float.
> What is the value that you want to use to filter the rows?
Alejandro,
I'm not sure how the OP got these values, or why SQL Server accepts
them, but I've had this issue in the past when working with the ATL OLE DB
libraries. If you forget to initialize a float before passing it in, that's
what you end up with (I think it's -infinity). It can be a real nightmare
to deal with... If you cast the values to VARCHAR, they show up as -1.#IND.
And at least in my case, SELECTing them w/o the CAST resulted in QA throwing
an error.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Adam,
Thanks for the explanation, it is very interesting.
AMB
"Adam Machanic" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:386B64F9-2E3C-4D90-BC38-E2DB3BA62478@.microsoft.com...
> Alejandro,
> I'm not sure how the OP got these values, or why SQL Server accepts
> them, but I've had this issue in the past when working with the ATL OLE DB
> libraries. If you forget to initialize a float before passing it in, that
's
> what you end up with (I think it's -infinity). It can be a real nightmare
> to deal with... If you cast the values to VARCHAR, they show up as -1.#IND
.
> And at least in my case, SELECTing them w/o the CAST resulted in QA throwi
ng
> an error.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>|||Thanks Adam! In my case the values where NaN's but I was able to correct it
now.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ex4sruDRFHA.612@.TK2MSFTNGP14.phx.gbl...
> Try:
>
> DELETE
> FROM YourTable
> WHERE YourFloatCol < CAST(-9.9999999999999998E+37 AS FLOAT)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:%23yhdsoDRFHA.2384@.tk2msftngp13.phx.gbl...
value.
> I
divide
>|||Joe,
In case you or anyone is still reading this thread, see
http://groups.google.co.uk/groups?q...ass+float_field
If you look at the IEEE floating point specification and at
CAST(YourFloatCol as binary(8)), you should be able to come up
with a way to filter these values out based on the binary value.
Steve Kass
Drew University
Joe wrote:

> Thanks Adam! In my case the values where NaN's but I was able to correct i
t
> now.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ex4sruDRFHA.612@.TK2MSFTNGP14.phx.gbl...
>
> value.
>
> divide
>
>