Mule ESB, formatting decimal format from expression node

Question

Prevously I had a result from a Database Connection SQL statement that resembled the following:

[{"BALANCE":111.11},{"BALANCE":222.12},{"BALANCE":444.30}]

And I used the following contents of an expression node to calculate the sum:

sum = 0;
foreach (row : message.payload) {
  sum += row['BALANCE'];
}
message.payload = sum;

This did not quite work out, but notice below that there are no quotes around the numeric variable that was returned

777.5299999999999994315658113919199

From an excellent answer from a previous thread, I switched to the following expression node contents:

sum = 0;
foreach (row : message.payload) {
  sum += row['BALANCE'];
}
message.payload = new java.text.DecimalFormat("#.##").format(sum);

This resulted the accurate result below:

"777.53"

My only problem is that it has quotes around the number. How can I eliminate the quotes?

Thanks

Do you want a string or a number? If a number, the first solution is correct. If a string, the second one is. — David Dossot, Jul 29 '14 at 16:01

score 1 · Answer 1 · answered Jul 29 '14 at 17:36

1

You may try converting the sum to BigDecimal and then set scale on it to 2.

message.payload = new BigDecimal(sum).setScale(2,java.math.RoundingMode.CEILING)

This keeps your payload as number instead of a String.

Hope this helps.

answered Jul 29 '14 at 17:36

user1760178

6,277
5
27
57

score 1 · Accepted Answer · edited May 23 '17 at 12:29

The real fix to your problem is the following: do not use floating point numbers to store currency amounts. This is prone to rounding errors (as you've experienced above) and it turns out people don't like to lose cents here and there.

Read https://stackoverflow.com/a/3730040/387927 for more on this.

So fix the database to store only integers, for example in cents, 111111 instead of 111.11, and only perform integer calculations on these cents.

Or review the DB query to return java.math.BigDecimal instances instead of the floats or doubles it seems to be returning. That way you would have no adverse rounding playing tricks on you.

Any other approach where you first would go through a lossy floating point data type and then perform rounding (like in user1760178's answer), will expose you to rounding issues.

Mule ESB, formatting decimal format from expression node

2 Answers2