Page 1 of 1
Sum of three columns is short by 1 cent, data is truncated
Posted: Wed Apr 28, 2004 11:07 am
by Hong-ching Tang
I have a DS job, where I get one of the column by add up the other three columns, ie. column d = column a + column b + column c, then insert them to a table. However, this simple calculation give 1 cent short than it should be for a lot of records. It did the insert but gave a warning msg. "FDRaudit..FDRTXN.fdrtxnhash_out: DSD.BCIPut call to SQLExecute returned informational message.
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Fractional truncation
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Fractional truncation". I have also send this data to a hash file, and there the addition looks fine. The data type of those columns is decimal 14.2 and the numbers are not big at all.
Posted: Thu Apr 29, 2004 12:44 am
by WoMaWil
what you should check is the "real" content your source and your target-columns. If you have 12,566 Euro as source this will be display in a 14.2 display as 12,57 Euro.
If you add 12,566 Euro + 12,566 Euro + 12,566 Euro you get 37,698 Euro
which is displayed in 14.2 as 37,70 Euro
If you add 12,57 Euro + 12,57 Euro + 12,57 Euro you get 37,71 Euro
which is displayed in 14.2 also as 37,71 Euro.
So have have a solution for the missing Cent.
So check your source and target and you will find for sure the solution. It is for sure nothing to blame DataStage for.
Wolfgang
Posted: Thu Apr 29, 2004 11:01 am
by Hong-ching Tang
My source columns A, B, C are decimal 17.2, so they also have 2 decimals specified. Do you still think that this could be the problem? Thanx.
Posted: Thu Apr 29, 2004 12:35 pm
by netboyks
We had the similar issue in our parallel jobs. We had to use the shared containers to do the aggregation or the addition rather than the aggregator stage. My suggestion is try using the shared container and test the output. Ascential came back to us telling that it is bug in 6.x and the release notes for 7.x tells that they fixed it.
Have Fun.
Netboyks
Posted: Thu Apr 29, 2004 2:07 pm
by nkumar_home
We have had many such issues in our jobs but mostly involving multiplication or division where the decimal precision changes. So we use the round function within Oracle so that we can have a predictable result rather than an arbitrary drop of precision.
Posted: Fri Apr 30, 2004 10:02 am
by Hong-ching Tang
We found that this happens when the source data is an integer. If it's an integer, instead of 10 it's 9.99999999999999...... and it's truncated as 9.99 hence missing 1 cent. In order to make sure that it will round up, I did this calc.: 0.01*int(0.5 + 100.00* [column A]) and this solved the problem. Thanks for your input here.