topic Use BigDecimal or DecimalType in tSQLRow in Talend Studio

Use BigDecimal or DecimalType in tSQLRow

Anonymous — Sat, 16 Nov 2024 04:08:46 GMT

Hi, I have been trying to get high precision values using tSQLRow in Talend but I can't seem to make it work. Be it any function such as AVG, STDDEV_SAMP, STDDEV_POP or simple Arithmetic operation such as division. I'm unable to get more than 17 precision. I do not wish to use traditional Talend components such as tMap and tAggregate because I have found them to be relatively slower on larger data sets.

Is there any way to perform these tasks using tSQLRow with high precision?

Ex:

Input Values: Value_1: 1457 | Value_2: 7456

Operation: (1457)/(7456)

Sample tSQLRow query:

SELECT

a.Value_1/a.Value_2 AS OUTPUT1,

CAST(a.Value_1/a.Value_2 AS decimal(38,16)) AS OUTPUT2

FROM row1 a

tSQRow is only able to provide me with 7 precision for the same while tMap gives the entire correct value. i.e.

0.19541309012875536480686695278969957082

Is there any way to increase tSQLRow's accuracy? Am I missing something? Can we use UDFs for the same? If so, how?

Any help is highly appreciated!

Talend Big Data Enterprise version 7.1

Re: Use BigDecimal or DecimalType in tSQLRow

Anonymous — Sat, 30 Nov 2019 01:14:36 GMT

Hi
Which DB type are you using? I guess we can't change the SQL API function, need further checking.
With Java code, we are able to get the expected precision:
new java.math.BigDecimal(value1)).divide(new java.math.BigDecimal(value2),18,java.math.RoundingMode.CEILING)

Regards
Shong

Re: Use BigDecimal or DecimalType in tSQLRow

Anonymous — Sat, 30 Nov 2019 02:34:48 GMT

Hi,

We're using flat files. Unfortunately, Java code isn't an option for us due to the performance impact it has. Isn't there any way to reach 38-39 precision in tSQLRow?