DataStage DOUBLE convert to TEXT trailing zeros 2 dec

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
iamcjej
Premium Member
Premium Member
Posts: 4
Joined: Wed Nov 28, 2018 9:59 am

DataStage DOUBLE convert to TEXT trailing zeros 2 dec

Post by iamcjej »

I am trying to convert a column that is DOUBLE input containing dollars and cents to a text file with no leading zeros and trailing zeros out to 2 decimals places. I am attempting this via a Transformer Stage, Stage Variable, but it is not working. Tried multiple ways, more and more creative, yet nothing gives correct result. Any thoughts? Much appreciated.

If we use Fix Zero we get leading zeros which we don't want. If we use Suppress Zero we don't get trailing zeros. We want the combination.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Use fix_zero in the stage variable.

Change the data type of the output column to VarChar, and use a Trim() function to trim the leading zeroes.

Code: Select all

Trim(svDecimalValue, "0", "L")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
iamcjej
Premium Member
Premium Member
Posts: 4
Joined: Wed Nov 28, 2018 9:59 am

Post by iamcjej »

Thanks, I appreciate the response but I am still not getting the desired result. Did I misinterpret? Steps taken:

1. Input to the transformer stage:
TOTAL_REV, Double
2. Stage variable Derivations and Stage Variables:
Keep_These_Rows, TOTAL_REV, DXEchange1
trim(DXEchange1, "0","L"), DXEchange2
3. Transformer Stage Properties, Stage:
DXEchange1, VarChar, 20,2
DXEchange2, VarChar, 20,2
4. Output of the Transformer stage:
DXEchange1,
DXEchange2
The output text is still dripping trailing zeros, even the trim function is dropping leading zeros:
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

The Trim() function, in that form, is intended to drop leading non-significant zeroes.

You may need an explicit DecimalToString() conversion to preserve the non-significant trailing zeroes.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
iamcjej
Premium Member
Premium Member
Posts: 4
Joined: Wed Nov 28, 2018 9:59 am

Post by iamcjej »

Thank you. I have resolved my problem. Since Money should always be represented by decimal, I changed it to DECIMAL (20,2), then used a combination of this function in the Transformer stage to display them as strings.
Trim(TrimF(DecimalToString(inputLink)),"0","L");
Post Reply