Comparison of DOUBLE data type in DB2

gvshruthi · New User Joined: 05 Jan 2009 Posts: 4 Location: bangalore

Dear All,

I was facing a very strange issue while querying on a DB2 table.
There are 2 volume values i get from 2 columns in 2 different tables and both the fields have the datatype as DOUBLE for the volume field.

I need to update a third table based on the below comparison:

If table1.volume < table2.volume, then update flag in table3 to 'L'.

But for some records where the volume is EQUAL in both table1 and table2, i still find that the table 3 record is updated to 'L'.

But say, if i CAST the 2 fields being compared to CHAR or INT type i do not get the problem. I get this only when i use the datatype DOUBLE.

Can anyone help me on how the comparison takes place for DOUBLE data type in DB2?

Akatsukami · Posted: Thu Jan 19, 2012 7:56 pm

DB2 data type DOUBLE is double-precision floating point (equivalent to COBOL COMP-2). It is unlikely that a decimal quantity can be represented without truncation issues that will lead to the result that you mention. I would inquire of the designer why this column needs to be floating point.