String Without Separators to Decimal Conversion

[former_member558803]

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:

1234500000
1230000000
1234567800

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.

[former_member558803 · Accepted solution]

Okay, the solution was crazy simple. I set the data type to decimal(10,2), removed the to_decimal function in the mapping, and added /100 after the substr function:

substr(Record,51,10)/100