Different precision in SQL selecting from a field with decimal places

Technote (troubleshooting)


Problem(Abstract)

When I try to select records from a database table with a select node using an equality with a real (float) value in the condition, the values in the SQL are generated with different precision to what I have specified in the select node in some cases.

Eg: I read from a Database source and after I have a select node with condition: KEY = 40.2

The SQL executed is:
SELECT T0."KEY" AS C0 FROM "XXX"."YYY" T0 WHERE
(T0."KEY" = 40.200000000000003)

Strangely this does not happen for all values, eg. with this in the select node:
KEY = 81.5

The SQL is generated as:
SELECT T0."KEY" AS C0 FROM "XXX"."YYY" T0 WHERE
(T0."KEY" = 81.5)

Resolving the problem

This is functioning as designed and is due to the fact that there is no exact representation of the value 40.2 as a floating point number.

40.200000000000003 is the value's closest possible representation of it to 15 decimal places.
This is therefore what is printed to the generated SQL.

Good practice is to not use floating point numbers (in)equality tests ie. instead of using:
test = 40.2
use:
abs(test - 40.2) < .0000000000001


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

SPSS Modeler
Modeler

Software version:

14.2, 15.0

Operating system(s):

Platform Independent

Reference #:

1642089

Modified date:

2013-06-26

Translate my page

Machine Translation

Content navigation