First of all, sorry for the cryptic subject, but the whole thing left me baffled..
Our client called regarding an incorrect "data" during a report, but the difference was just something shy of a cent..
I thought "oh well, maybe they're wrong, but better be sure".. boy, I didn't know what i was plunging into..
After some wild guess and double check I arrived at the root of the problem: documentum way of handling double...
a simple
document.setDouble("myattribute", 2859782.578)
translated on db level in ah horrid 2859782.578125 stored into the db..
SURE! rounding the number up or formatting it via a custom formatter solves the issue, after all the number is simply roundable to the appropriate value..
BUT!
when i do a simple dql query say a
select suppliercode as supplier_code, sum(firstoffer) as numero fromobjecttype where firstoffer != 0 AND (workflowstate condition blablabla') AND DATEDIFF(year,"protocoldate",DATE(NOW)) < 1 and suppliercode = '5686' group by suppliercode
guess what happens?
It will sum those INCORRECT numbers thus
a previously "correctly roundable" (but formally incorrect) 2859782.578125 summed to another "correctly roundable" (but formally incorrect) "take another value with floating component with unrequired numbers"...
And there you go, disaster on earth, with wrong decimals ending up summed to other wrong decimals inflating the number to the point of nonreturn thus resulting in incorrect numbers (if only by a few cent.. but I'm handling a e-procurement app, so being wrong about numbers is not acceptable even if it was 1/billionth of a cent....)..
Is there some solution? Because yeah I can post format the value or handle it correctly when java is involved, but I can't find any roundup/rounddown function in dql to solve the issue and the alternative is to get for EACH element of the possible group by a collection, then format PROPERLY the double value and then sum it up... Thus making the whole thing a LOT heavier considering the raw amount of data involved...
It all stems from the fact that when given a source value of a double (taken from an oracle db, i doublechecked the source value) I simply do a
newsaf.setFirstoffer(rs.getFloat("SUPPLYYEARLYVALUE")); (the source is a float)
and later I do another
document.setDouble("firstoffer", migrating.getFirstoffer()); (migratinf.getFirstoffer gives me a float)
Sure using a float as a double isn't really "canon" but I don't expect it to generate such issue...
I even tried to simulate the issue...
float floatnumber = Float.valueOf("13669126.078");
System.out.println("value float: "+floatnumber);
double doublenumber = testvito.forzare(floatnumber);
System.out.println("value double: "+doublenumber);
And the result is:
valore float: 1.3669126E7
valore double: 1.3669126E7
so the issue is introduced by documentum apparently...
Am I doing anything wrong, or it's a known issue?
Thanks,
Vito
