Logic operator not working properly

Question

Logic operator not working properly

umut ozkan

Grasshopper

Points: 12
More actions
February 6, 2012 at 3:11 am

#254952

Column data type is float. When i select all table data, column value looking equals to 100. when i trying select equals to 100. nothing returned. If i chance the condition to CAST(ln.PRODAMOUNT as decimal(8,2))=100 its working. This is floating point issue or what? Something wrong on float value type?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

umut ozkan (2/6/2012)
Column data type is float. When i select all table data, column value looking equals to 100. when i trying select equals to 100. nothing returned. If i chance the condition to CAST(ln.PRODAMOUNT as decimal(8,2))=100 its working. This is floating point issue or what? Something wrong on float value type?

Yes. Float is an approximate data type, while decimal is an exact data type.

float and real (Transact-SQL)

Using decimal, float, and real Data

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

umut ozkan Grasshopper Points: 12 More actions · Answer 2

Thanks. If i change data type on table now, might lose any data?

umut ozkan Grasshopper Points: 12 More actions · Answer 3

i have tried "ln.PRODAMOUNT=CONVERT(FLOAT,100)". Its not worked.

also ROUND(ln.PRODAMOUNT,0)=100 its worked.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

umut ozkan (2/6/2012)
i have tried "ln.PRODAMOUNT=CONVERT(FLOAT,100)". Its not worked.
also ROUND(ln.PRODAMOUNT,0)=100 its worked.

That makes sense.

Floating point is approximate.

Meaning, it can be that the value is actually 99.999987 for example. When you convert 100 to float, you probably get 100.0, thus not resulting in an exact match. Rounding the float number to an integer might work, as you found out.

What you can also do is the following:

WHERE ABS([FLOAT Column] - CONVERT(FLOAT, 100)) < ThresholdValue

Where ThresholdValue is a very small digit. You can choose this, depending on how accurate you want your results to be.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP