How to provide fixed format flat file in PX
- A Datastage PX primer by Teej
(Hey, if Ken can do a big primer, I can!)
1. This solution will work as long as you output only integer or characters. You will need to convert your input fields either those two fields as needed.
2. The Format column for 6.0 is not used. I do not know whether 7.0 does use it.
3. You need to have a Transform stage immediately before the Sequential File output.
* * *
For the Sequential File Stage, you need to set the following values to ensure that the file is fixed. Click on the "Format" tab and set the following values for Record Level:
Record Delimiter = "UNIX newline" (if you're on UNIX)
Record length = fixed
For the Field defaults, you need to determine if you need to have a delimiter and/or quotes. If not, set both as "none."
Nothing else should be set up. I have seen use of the Type Default, but with the variations on my fields, I rarely can find a situation where it addresses EVERYTHING.
* * *
If you have an output exclusively set as varchar, the task is really simple, set the transform output for all fields to "CHAR" and the set length for each field. You MUST set all fields as NOT Nullable.
Within each field's code, you will have to determine whether you wish to Right-justify or Left-justify your data. You can use the Left(), Right(), Space(), and PadString() functions to handle the formatting.
Finally, you must handle the nullability possibility:
If IsNull(field) Then Space([size of field]) Else [formatting behavior for field]
* * *
The integers are a bit more tricky. Ensure that the fields are NOT Nullable. If you want the space to be blank, you will need to convert that field to Characters, and handle the null value appropriately as detailed above.
In the Sequential File Stage, click on "Columns" tab, and then double-click the column number next to the field you are fixing (Integers only.) You should see "Edit Column Meta Data" screen. Add two fields to the Integer Type folder under Properties:
Out_Format = %[size]d (for example %10d for a 10 digit field)
Field width = [size]
This will provide a right-justified non-zero-filled integer field.
* * *
Now how do we properly convert Decimal fields to Char? *chuckles* That's a subject that I personally struggled with for a while until I came across this solution.
In this situation, I'm trying to output a properly formatted dollars and cents, so I need to have 2 decimal places at all time. So this is pretty big. This is a field that is 6 character long, so adjust accordingly:
Code: Select all
If IsNull([field]) Then
Space(6)
Else If ([field] = 0) Then
Space(2) : "0.00"
Else If ([field] < 0) And (Abs([field]) < 0.10) Then
Space(1) : "-0.0" : Right([field], 1)
Else If ([field] < 0) And (Abs([field]) < 1.00) Then
Space(1) : "-0." : Right([field] * 100,2)
Else If ([field] < 0) Then
Right(Space(6) : "-" : (Field(Trim(Trim([field], "-", "L"), "0", "L"), ".",1) : "." : Right([field] * 100,2)), 6)
Else If ([field] < 0.10) Then
Space(2) : "0.0" : Right([field], 1)
Else If ([field] < 1.00) Then
Space(2) : "0." : Right([field] * 100,2)
Else
Right(Space(6) : (Field(Trim(Trim([field]), "0", "L"), ".",1) : "." : Right([field] * 100,2)), 6)
I will leave the background up to you folks as an exercise. The problem with 6.x is that DataStage PX internally use leading and trailing zeros, and there is no functions to trim that available for PX. Trim is for space characters only. I am sure that 7.x will have that function, so obviously the above code will be simplified.
If you have any other complex requests, consulting is available.
-T.J.