Ticket #30155

Question

I have a sales report where the revenue is stored as a number in a character field. For this example it is stored as "00000080280000". Is there a way to get this value to be formatted in dollars and cents, so it reads $802.80? I tried creating a calculated field dividing the revenue field by 100000, but this seems to round to $802.

Answer

Informed customer she is working with a character field that happens to be storing a numeric value. Performing database arithmetic on a character field should never be attempted, as this is either going to produce a database error, or give inconsistent results. A possible approach she can apply is to create a numeric type calculation, that will convert the character field to a decimal, and divide the result by 100000. Then she can pick any format (including $ and cents) for the calculation, and even better she'd able to perform any necessary arithmetic or filtering off of the newly created calculation.
For AS/400 databases, there is function called DECIMAL() that allows you to cast a string to a numeric value with decimal precision. See here for more info on this function.
https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_decimal.html

Asked on March 25, 2020