topic Re: Clarification regarding preferred Datatypes used in Talend in Talend Studio

Clarification regarding preferred Datatypes used in Talend

BA621858188 — Thu, 01 Sep 2022 09:31:33 GMT

Hi All,

1) what datatype in Talend we can use for currency data type. as per below page, we should not go with float/double.

https://talendweb.wordpress.com/2017/04/26/talend-data-types/

2) what is the datatype we can use if we want the precision as 12 and scale as 2 in the target datatype.

ex: target value may be

112

100.45

1023.1

Thanks in advance

Re: Clarification regarding preferred Datatypes used in Talend

Anonymous — Thu, 01 Sep 2022 14:02:03 GMT

BigDecimal is the class to use for currency. Take a look at what Java say about this class.

With regard to your precision, this should be managed by the DB. The BigDecimal will be able to hold the data accurately to be sent to the DB.

Re: Clarification regarding preferred Datatypes used in Talend

BA621858188 — Fri, 02 Sep 2022 09:33:12 GMT

@Richard Hall ,

Thank you for quick response.

My Target is .dat file and they want the exact data type which can fill the data as below. decimal only where it is required.

112

100.45

1023.1

Can you help here.

Thanks,

Re: Clarification regarding preferred Datatypes used in Talend

Anonymous — Fri, 02 Sep 2022 12:32:43 GMT

Ah, OK. A .dat file is either plain text or binary. I will assume that it is plain text rather than binary. If this is the case, you do not need to worry about a "type" for adding this data. The processing of the data within the Job will need it to be carried out using a BigDecimal column. This will maintain its accuracy. But when it goes to the .dat file, all you need to do is format the BigDecimal as String in whatever way you want it.

The example shown here should enable you to do what you want.....

https://stackoverflow.com/questions/10269045/format-a-bigdecimal-as-string-with-max-2-decimal-digits-removing-0-on-decimal-p

Re: Clarification regarding preferred Datatypes used in Talend

BA621858188 — Sat, 03 Sep 2022 06:46:25 GMT

@Richard Hall ,

Thank you for response.

As part of testing, I have created 3 additional schema in the tfileoutputdelimited component for .DAT file.

3 colums storing same values but different data types

-big decimal(12,2)

-float(12,2)

-double(22,2)

my o/p is as below

-for decimal , the values are getting rounded off and for the value at source without decimal for ex. 540 for that I am getting 540.00. both is not as per the requirement.

-for float, It is also giving value by doing round and appending . for the values that dont have decimal in it.

-for double, It not rounding but adding .00 for the values that dont have decimal in it.

Re: Clarification regarding preferred Datatypes used in Talend

BA621858188 — Tue, 06 Sep 2022 13:28:33 GMT

@Richard Hall ,

which one should I go for then out of above 3?

Regards,

Re: Clarification regarding preferred Datatypes used in Talend

Anonymous — Mon, 12 Sep 2022 22:48:56 GMT

Hi @Vrushabh Malbari,

Sorry about the late response, I have been on vacation. Since your requirements are pretty rigid and the dat file is a text file, I think the best way to do this is to format the BigDecimal to a String after all of your calculations have taken place and before you write to the file.

An example of doing this can be seen below....

BigDecimal bd = new BigDecimal(123.454665); //<-- Ignore this line, it is just preparing a new BigDecimal. You will have this.

System.out.println(bd.setScale(2, java.math.RoundingMode.HALF_UP).toString()); //<-- The System.out just prints the output to the output window.

In this code.....

bd.setScale(2, java.math.RoundingMode.HALF_UP).toString()

....the bd is the BigDecimal, the setScale is a method to format it and the toString outputs the value as a String.

Re: Clarification regarding preferred Datatypes used in Talend

BA621858188 — Tue, 13 Sep 2022 17:19:41 GMT

@Richard Hall ,

I guess I should you bigdecimal or decimal with precision and scale as .00 for every value is fine @target as per new requirements.

one more question, I have records like True and False as varchar in source but at Target I want T and F (Boolean) what expression I can use in tmap for this?

Appreciate your help.

Re: Clarification regarding preferred Datatypes used in Talend

Anonymous — Tue, 13 Sep 2022 17:40:33 GMT

A varchar is a String. So you can simply use a tMap with an expression like this....

row.booleanColumn.equals("True") ? "T" : "F"

You are writing to a file, so all of your output schema can be made up of Strings representing your values.

Re: Clarification regarding preferred Datatypes used in Talend

BA621858188 — Thu, 15 Sep 2022 07:41:19 GMT

@Richard Hall

yes. agreed on that point but the requirement is to have Boolean datatype at the target schema.

But its not possible because T and F is not considered as Boolean in Talend.

Any other easy way?

Re: Clarification regarding preferred Datatypes used in Talend

Anonymous — Thu, 15 Sep 2022 09:51:26 GMT

If the target is a flat file (a file that is just rows of characters) then the target data type doesn't matter. It is just a series of Strings as far as Talend is concerned.

If an output file has data like this....

12, Hello how are?, 12/03/1999, 23.897623, True

.... the columns may have had the types of .....

Integer, String, Date, Double, Boolean

..... but as far as the output is concerned, they are written as a String to the file.

So this.....

row.booleanColumn.equals("True") ? "T" : "F"

....will be fine.

Re: Clarification regarding preferred Datatypes used in Talend

BA621858188 — Sun, 18 Sep 2022 15:54:30 GMT

@Richard Hall ,

you mean

row.booleanColumn.equals("True") ? "T" : "F" using this function and output datatype keeping as String.

Re: Clarification regarding preferred Datatypes used in Talend

Anonymous — Mon, 19 Sep 2022 16:48:54 GMT

Yes