It seems that you want to do a Cartesian join between TBL_B and TBL_A. Assuming that the EFFDT is dependent on EMPLID alone (that is, OPRID has no effect on it, whatsoever), you need to do the following -
1. Create a hashed file with the columns, DummyID, EMPLID and EFFDT, with keys on the first 2 ID columns.
2. Load TBL_A into this hashed file, with the value 1 assigned to DummyID. This eliminates all duplicates of EMPLID.
3. Load this hashed file into a sequential file, say File_1, with DummyID (only) as the key.
4. Load TBL_B into a sequential file, say File_2, with columns DummyID2 and OPRID2. Just as in step 2, assign a value 1 to DummyID2.
5. Use a Merge Stage with these 2 sequential files as input, and with Join Type set to INNER JOIN. In the Mapping tab, make sure you specify the key as DummyID for File_1 and DummyID2 for File_2 so that a Cartesian Join will be performed since all key values are 1.
5. The end result is you have a file -
Code: Select all
DummyID EMPID EFFDT DummyID2 OPRID2
------ ---- ----- ------- -------
1 1 01/01 1 JKL
1 2 01/01 1 JKL
1 3 03/01 1 JKL
1 1 01/01 1 MNO
1 2 01/01 1 MNO
1 3 03/01 1 MNO
You can now, extract the columns OPRID2, EMPID and EFFDT into another file and append it to your TBL_A rows. Voila!!