Decimal to String keeping 2 decimal places digits
Posted: Fri Dec 14, 2018 1:24 am
Good day,
my goal is to convert a decimal(32,15) into a CHAR(20) and writes it in a fixed length sequential file.
The relatively simple job reads data from a teradata table, sends data to a transformer, writes result to text file. Here follows a view of the job:
https://imgur.com/PhN0UUE
Example
decimal input: 290.849,000000000000000
char output: +0000000000290849,00
I need to have the sign, the number with no thousand separator, the comma, 2 decimal places digits no matter what the digit is (no rounding if it is 0).
I tried the following:
1) if IsNull(my_field) then '' else If my_field >= 0 then '+':Trim(Str("0", 19 - Len(DfloatToStringNoExp(FAbs(my_field),2))):DfloatToStringNoExp(FAbs(my_field),2)) else '-':Trim(Str("0", 19 - Len(DfloatToStringNoExp(FAbs(my_field),2))):DfloatToStringNoExp(FAbs(my_field),2)) --> PROBLEM: no control over choice of number of decimal places to keep
2) if IsNull(my_field) then '' else If my_field >= 0 then '+':Trim(Str("0", 19 - Len(DecimalToString(FAbs(my_field))):DecimalToString(FAbs(my_field))) else '-':Trim(Str("0", 19 - Len(DecimalToString(FAbs(my_field))):DecimalToString(FAbs(my_field))) --> PROBLEM: does not even display the converted field
3) converted the input field directly in the select statement as follows: cast(my_field as DECIMAL(32,2)) as my_field, to have control on number of decimal places, but unfortunately when digit = 0 is truncated. Example:
+0000000000000313585
+00000000006503437.1
is there a way of achieving my desired output using the datastage transformer (or any othe datastage component)?
Thank you very much,
my goal is to convert a decimal(32,15) into a CHAR(20) and writes it in a fixed length sequential file.
The relatively simple job reads data from a teradata table, sends data to a transformer, writes result to text file. Here follows a view of the job:
https://imgur.com/PhN0UUE
Example
decimal input: 290.849,000000000000000
char output: +0000000000290849,00
I need to have the sign, the number with no thousand separator, the comma, 2 decimal places digits no matter what the digit is (no rounding if it is 0).
I tried the following:
1) if IsNull(my_field) then '' else If my_field >= 0 then '+':Trim(Str("0", 19 - Len(DfloatToStringNoExp(FAbs(my_field),2))):DfloatToStringNoExp(FAbs(my_field),2)) else '-':Trim(Str("0", 19 - Len(DfloatToStringNoExp(FAbs(my_field),2))):DfloatToStringNoExp(FAbs(my_field),2)) --> PROBLEM: no control over choice of number of decimal places to keep
2) if IsNull(my_field) then '' else If my_field >= 0 then '+':Trim(Str("0", 19 - Len(DecimalToString(FAbs(my_field))):DecimalToString(FAbs(my_field))) else '-':Trim(Str("0", 19 - Len(DecimalToString(FAbs(my_field))):DecimalToString(FAbs(my_field))) --> PROBLEM: does not even display the converted field
3) converted the input field directly in the select statement as follows: cast(my_field as DECIMAL(32,2)) as my_field, to have control on number of decimal places, but unfortunately when digit = 0 is truncated. Example:
+0000000000000313585
+00000000006503437.1
is there a way of achieving my desired output using the datastage transformer (or any othe datastage component)?
Thank you very much,