Sum of three columns is short by 1 cent, data is truncated

Hong-ching Tang · Post by **Hong-ching Tang** » Wed Apr 28, 2004 11:07 am

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.

WoMaWil · Post by **WoMaWil** » Thu Apr 29, 2004 12:44 am

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

Hong-ching Tang · Post by **Hong-ching Tang** » Thu Apr 29, 2004 11:01 am

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.

netboyks · Post by **netboyks** » Thu Apr 29, 2004 12:35 pm

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

nkumar_home · Post by **nkumar_home** » Thu Apr 29, 2004 2:07 pm

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.

Hong-ching Tang · Post by **Hong-ching Tang** » Fri Apr 30, 2004 10:02 am

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.