So I know why (7.8/39)=.2 does not return results in a where clause in SQL while ROUND((7.8/39),1)=.2 will return results, but I don't know how to explain it clearly to someone I work with and I would like to provide them something to read other then me telling them that that will not work as part of their where clause.
Thanks you,
Jalal
This is due to floating point arithmetic. This can be hard to explain, if you don't just "get" it.
Where do I start? Integers are pretty easy to represent in the bits that a CPU understands. So, 00000101 is interpreted as 5 -- and exactly 5 -- because it is 2^2 + 2^0.
However, this doesn't work for fractional parts of numbers. To solve this, computer scientists invented two forms of numbers. One is what I personally think of as BCD (binary coded decimal) but databases call decimal or numeric. Each digit is represent as a number. You only need 4 bits for a digit, so this looks like:
0011 0001 1111 0000 1001
3 1 . 0 9
This exactly represents 31.09. Just keep adding bits. Note: This is conceptual. Exact implementation may vary.
The second method is exponential notation. That is: xxx * 2^ yyy, where xxx and yyy are integers. For example, 0.25 is 1 * 2^(-2). The "1" and "-2" can be exactly represented.
This works well for approximations of numbers. The problem is that 0.25 can be exactly represented. But 0.24 and 0.26 cannot be. They end up with some complicated numbers being involved. The same is true of 0.2 -- which is the number you are trying to represent.
What happens is that you write 0.2 and it is represented as 0.00110011001 (say). But when you do the calculation it ends up being 0.00110011000. Oooh. That last bit changed, so it is really more like 0.19997 (well, a bit more '9's in practice). The values are not exactly equal.
The moral: don't use equality on floating point numbers. The numbers may look the same but differ in some piddling binary decimal place.