How to trim numerical strings as per database column precision

[ossDataEngineer]

Hello,

I get numeric data in string format, which I want to save in Oracle database column with datatype NUMBER(18,2). Is there a way I can skip the rounding in database?

Original data: 278.7182
Current table data after hop processing: 278.72
Expected table data: 278.71

Original data: 278.7812
Current table data after hop processing: 278.78
Expected table data: 278.78

Original data: 278.7899
Current table data after hop processing: 278.79
Expected table data: 278.78

[tlue]

I guess you need some.more examples or know more exactly what you want. I guess your DB is doing the usual European half up rounding. Ecxel by default often does a down or just cut off as often used in USA. A Calc step could maybe behave this way too.
Have a look i.e. here as Hop is a java software.
https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html

Your two example numbers could be done with substring cutting off the last two letters an then let the DB auto convert it, not needing any further rounding any more to be done.

[hansva]

In the select values transform you can change the rounding type on your number columns in the metadata tab, though I haven't tested that with table output transform it might work. Make sure you also set the precision you want (2 decimals)

[ossDataEngineer]

@hansva - I tried using select values shape to convert string to number with precision and rounding down

Seems like Oracle table still tries to round up the values. Preview data on select values shape shows correct rounded down data, but ultimately Oracle table gets it rounded up.

But when I add one more select values shape with string datatype reconversion, the data in oracle table gets trimmed to precision.

Question:
Do you see any problems with this approach where I use 2 select values shape?

[hansva]

I don't see an issue in that, I would add a feature request to have table output (or all DB related transforms) to adhere to rounding and precision when writing to them. I know it works for text file output, was hoping it would work here too. But it might only happen when we convert the numbers to strings like you are doing now.