DSXchange

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,

Well, being the curious soul that I am... let me throw something out there. For a relatively simple something like this which apparently has no need for any parallel features, why not use a Server job or a BASIC transformer in your PX job? It falls into that "other DataStage component" category and you would have full control over the output that way. Or does that violate one of your company's "Thou Shalt Not" commandments?

Hi Craig,
thanks for your suggestion (and apologies for late reply). Unfortunately I could not do as suggested. I managed it this way:

1) rounded to 2 decimal places in the select statement using the following formula: TRIM(CAST(my_field as DECIMAL(32,2) FORMAT 'ZZZZZZZZZZZZZZZZ9.99')) as my_field_calc

2) pass my_field_calc to the transformer stage as char

3) pass through the original my_field to the transformer stage as it was (decimal)

4) pad my_field_calc with zeros accordingly to desired length and sign accordingly with original my_field sign

This may not be the most elegant approach, but very effective. And time was not on my side, so I had to end it quickly.

I would consider this a workaround.
Thanks again,

Hey, whatever works for you is what matters.