decimal number format issue in fixed length file

[fyaqq]

Hi, folks:
In one of my job, I need to generate fixed length files, and I had some issue
with decimal numbers.
The requirement is as follows.
1. for decimal number specification like 6.11, I need to generate a number
that has 6 numbers before the decimal point, and 11 numbers after the
decimal point ( all padded with 0s ).
2. for decimal number specification like 6.11-, in addition to 1 above, and based the sign of the number, it is a space for positive number , and - for negative number.

Here is an example, for number 1.0, and for specification 1, I need to export 000001.00000000000, and for specification 2, I need to export
000001.00000000000_, ( I uses _ to emphasize the space I need to generate).

Any thought? Thanks a lot!

[ray.wurlod]

The space character indicates a positive (or non-negative) value. However, trailing sign is not directly supported in parallel jobs. (It can easily be done in server job or BASIC Transformer stage.)

The first question, though, is to make sure that your target field is a string (not a number). Without that you lose any control over the format of the number.

After that, simply use DecimalToString() to convert the absolute value of the number to string, then concatenate either " " or "-" depending on whether the value is negative.

Code: Select all

DecimalToString(Abs(InLink.TheNumber)) : (If InLink.TheNumber < 0 Then "-" Else " ")

[fyaqq]

Yes. In server job, I can use Iconv to get the desired results.

Another question. For parallel job, I noticed by default, the sign is in the
leading place.

For example, number 1.0, using specification 6.11, I got _000001.00000000000 in the output ( notice the leading space ).
What I actually want is 000001.00000000000 (without leading space)

Is there any way to get rid of the leading space in decimal output in fixed-width file?

Thanks.

[chulett]

Define the field as a string, then you have full control over its format.

[fyaqq]

Define the field as a string, then you have full control over its format. ...

But my source field is decimal, can you elaborate a little bit on how to
do this?

[ray.wurlod]

Change it to a string within your DataStage job.

[fyaqq]

Change it to a string within your DataStage job. ...

Still confused

Since I wanted to write decimal to sequential files, if I know how to
convert the source decimal to string, I am very happy now.

After hours of fiddling with this issue, below is what I have observed for sequential file stage. Can someone agree or disagree on them?

• 1.by default, decimal numbers will be written to the file as [ |-]ddd.ddd
  format. i.e, for a positive number, the leading character is a space, for a negative number ,it is -.

• 2. in format tab, you can choose type-defaults->decimal packed=no (separate) and sign position = trailing, to get a trailing sign.

For 2, I find the decimal separator does not show up in the generated sequential file. Is it the expected behavior?

[chulett]

Yes, expected. Declare your output field as Char and then use DecimalToString() to get whatever output format you desire.

[fyaqq]

Yes, expected. Declare your output field as Char and then use DecimalToString() to get whatever output format you desire. ...

Ok. Many thanks chulett.

I used your approach to define the output as char with appropriate length.
The workaround I use to add the trailing sign is to check if the decimal number from source is negative. If yes, I will append '-' to the output, otherwise I will append a space ' ' to the output.

And I use derivative substitution to change many affected columns.

[chulett]

Excellent. :D