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.
Sum of three columns is short by 1 cent, data is truncated
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 9
- Joined: Tue Nov 18, 2003 4:03 pm
- Location: Pleasanton, CA
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
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
-
- Participant
- Posts: 9
- Joined: Tue Nov 18, 2003 4:03 pm
- Location: Pleasanton, CA
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
Have Fun.
Netboyks
-
- Participant
- Posts: 19
- Joined: Fri Apr 02, 2004 10:13 am
-
- Participant
- Posts: 9
- Joined: Tue Nov 18, 2003 4:03 pm
- Location: Pleasanton, CA
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.