This seems like a really simple thing to fix but for the life of me I can't find the solution. I'm processing a file from a merchant that has some dollar amounts in it, which look like this:
I have the output column set to decimal(10,2), and a conversion function in the mapping to_decimal(substr(Record,51,10),'.',',',2) but the output is always two decimal places off. I tried changing the substr length to 8, and the last two digits are replaced by zeroes.
Surely someone has come across this issue in the past. It seems really straightforward but I'm not getting the functions to work properly--even with variable precision it shouldn't be this difficult.