DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
audev
Participant



Joined: 18 Jul 2018
Posts: 11

Points: 327

Post Posted: Fri Dec 14, 2018 1:24 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Additional info: problem converting a decimal(32,15) into a char(20)
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,
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42985
Location: Denver, CO
Points: 221730

Post Posted: Fri Dec 14, 2018 6:39 am Reply with quote    Back to top    

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

_________________
-craig

Help I'm steppin' into the twilight zone, place is a madhouse, feels like being cold
My beacon's been moved under moon and star, where am I to go now that I've gone too far?
Rate this response:  
Not yet rated
audev
Participant



Joined: 18 Jul 2018
Posts: 11

Points: 327

Post Posted: Sun Jan 27, 2019 10:27 pm Reply with quote    Back to top    

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,
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42985
Location: Denver, CO
Points: 221730

Post Posted: Mon Jan 28, 2019 2:38 am Reply with quote    Back to top    

Hey, whatever works for you is what matters.

_________________
-craig

Help I'm steppin' into the twilight zone, place is a madhouse, feels like being cold
My beacon's been moved under moon and star, where am I to go now that I've gone too far?
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours