sql decimal datatype

[ejoseph]

I'm using labview 7.1 with the database connectivity toolkit to interact with a sql server.  One column is of the datatype float, but when I send 0.95, it stores 0.94999999.  I've read about this before, but trying to use numeric or decimal causes a different problem.  When the column is of one of these datatypes and I do a fetch of the data, nothing in that column comes back.  I'm leaving the data as a variant in labview, so it isn't a conversion issue.  I get just a blank variant box.  It works only for float.  And the other columns all work fine. 

Are decimal and numeric types not supported by labview, and if so how do I get around the inaccuracy of float datatypes?

[altenbach]

Fractional floating point numbers can often not be represented exactly in binary, even if they are nice and round in decimal.

0.95 for example is actually 0.949999999999999955(DBL) or 0.9499999880791(SGL). There is nothing wrong with that. You simply need to set your display to only show the desired number of significant digits.

---

@ejoseph wrote:

Are decimal and numeric types not supported by labview, and if so how do I get around the inaccuracy of float datatypes?

---

There is no real inaccuracy. The difference between 0.94999999 and 0.95 is 0.00000001 or about 1 in 100000000! (This is just the diplayed part, the difference in real life is even less. Is your number really more precise that 52bits? (or 23bits for SGL).

[ejoseph]

The problem isn't the accuracy.  It's when I go to search the table.  If I do a

select * from table_name where column_name = 0.95

I get nothing back.  That's because the value is 0.949999999.  There is no way I'm going to be typing that into a query.

[Dennis_Knutson]

You can use the BETWEEN clause. Something like SELECT * from table_name WHERE column_name BETWEEN .949 AND .951.

[ejoseph]

I know there are work arounds, I was just hoping I didn't have to use them because they would complicate every future query.  So is there no way to read a decimal or numeric from a sql database into labview?

[Dennis_Knutson]

I don't think its a work around and the issue has nothing to do with LabVIEW. If you store data in a column defined as a floating point number and insist on doing a query based on a value in that column, you can't use the '=' clause. There is no problem reading a floating point number itself from SQL Server. It's just the WHERE clause that is tripping you up. Another 'work around' would be to define the column as text and store the numbers that way.

[ejoseph]

I see your point.  It's not a work around, it would just slightly complicate future queries and anyone who comes along later would have to know about that issue.  Before I do something, like use a varchar datatype or some other method, am I correct in my assumption that Labview can't accept a decimal or numeric from a sql database?  I just want to make sure, because I don't know if I might have things set up incorrectly in the database or if there is something I'm missing in Labview.  I'm very new to databases. 

Thank you all for your responses.

[ejoseph]

After working with an NI engineer, he confirmed that a decimal type in sql will return a blank column to labview.  But (according to the engineer):

"When I say it is "blank", it is not viewable in a variant indicator but does indeed hold valuable data."

So what this means is that you can't leave it as a variant indicator.  You need to use the Variant to Data.vi in order to see the data.