CFF and merging records back together

[PilotBaha]

I have a CFF stage that's receiving data from a COBOL copy book with REDEFINES and OCCURS clauses. (Brings back memories from old days).
What i'd like to do is to produce some kind of a sequential record number so that i can match the records together later on.

To give you an example here's the input data :

Code: Select all

###Header aaa bbb ccc
BIL John Doe 124 main st. 
SHP John Doe 5445 good ave. 
DRP Jane Doe 2345242 tttyyy %10 gghgh 
### header aaa bbb ddd

In this case the header record should be bypassed but BILling, SHiPping and DRop Ship data will have their own look ups and quality checks. (So, they go to different processing paths)

Since the first ### header record defines a group eventually i would like to group the data together the same way that they came in.

A single join between the different streams are not going to give me what i want. If this was a server job, i'd add @INROWNUM to the flat file and eventually merge them together according to their record sequence number. The problem is i cannot use in multi node PX and when i have different levels of OCCURS .

Any ideas on what would be a good solution to this?

Thanks..

[crouse]

if what you're after is generating a surrogate key, much like @INROWNUM, but in PX... do a search for surrogate key generation (not using the surrogate key stage) and a poster of "vmcburney". Look for the post that uses a stage variable and pay close attention to the initial value setting for the stage variable

[PilotBaha]

Craig,
that also crossed my mind. Creating some stage var with SeqNum = SeqNum +1 type of derivation and an initial value of 1. I won't need to check the max value from the database as these records will be used one time and they will be for my internal processing.
For some reason i remember this method being ok for server but not for EE/PX/whachawannacallit.

Thanks..

[crouse]

it will work, trust me, and read vincent's post

[chulett]

Trust me, my pretty. [rubs hands together slowly]