Clarification regarding preferred Datatypes used in Talend

[BA621858188]

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

[Anonymous]

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.

[BA621858188]

@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,

[Anonymous]

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

[BA621858188]

@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.

[BA621858188]

@Richard Hall​ ,

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

Regards,

[Anonymous]

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.

[BA621858188]

@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.

[Anonymous]

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.

[BA621858188]

@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?