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?
Joined: 03 Oct 2009 Posts: 1746 Location: Bloomington, IL
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.