decimal number format issue in fixed length file
Moderators: chulett, rschirm, roy
decimal number format issue in fixed length file
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!
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!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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 " ")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Still confusedray.wurlod wrote:Change it to a string within your DataStage job. ...
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.
Ok. Many thanks chulett.chulett wrote:Yes, expected. Declare your output field as Char and then use DecimalToString() to get whatever output format you desire. ...
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.