IBM Mainframe Forum

i,

I have 2 files I need to join/merge to create records missing from the second file based on what's in the first file.
My experience to date of Joins etc hasn't included anything like this, so I'm not sure if it's even possible.

The file layouts are very much simplyfied here but the objective is the same:
Input File 1
Product1 Combination1
Product1 Combination2
Product1 Combination3
Product2 Combination1
Product2 Combination2

Input File 2
Product1 Dealer1 Combination1
Product1 Dealer1 Combination2
Product1 Dealer1 Combination3
Product1 Dealer2 Combination1
Product1 Dealer2 Combination3
Product1 Dealer3 Combination3
Product2 Dealer1 Combination1
Product2 Dealer2 Combination2
Product2 Dealer3 Combination1

I need to create a version of file 2 that has an occurrence of each combination per dealer per product. i.e. the output should look like :
Product1 Dealer1 Combination1
Product1 Dealer1 Combination2
Product1 Dealer1 Combination3
Product1 Dealer2 Combination1
Product1 Dealer2 Combination2
Product1 Dealer2 Combination3
Product1 Dealer3 Combination1
Product1 Dealer3 Combination2
Product1 Dealer3 Combination3
Product2 Dealer1 Combination1
Product2 Dealer1 Combination2
Product2 Dealer2 Combination1
Product2 Dealer2 Combination2
Product2 Dealer3 Combination1
Product2 Dealer3 Combination2

I am thinking I would need input file 2 sorted as above, by product, dealer then combination. But then a join on Product and combination would not work correctly. I haven't yet used GROUPs, is somethign like that needed here?

Or is what I want even possible? If it takes more than 1 step, that would be fine. SYNCSORT FOR Z/OS 1.3.2.2R in use.

Many thanks in advance.

Hello adelewalsh,

Here is a SyncSort for z/OS job that will produce the requested output:

Hi Alissa,

I am reading your Joinkeys codes in this site. If you would have time could you kindly explain how this works:

JOINKEYS FILES=F1,FIELDS=(1,8,A)
JOINKEYS FILES=F2,FIELDS=(1,8,A)
REFORMAT FIELDS=(F1:1,9,F2:10,8,F1:10,12)
SORT FIELDS=(1,29,CH,A)

I'm trying to use this as a basis for getting the specific records that are in File2 but not in File1:
File1:(key: column 3-13)
12AAAAAAAAAAAAA
90BBBBBBBBBBBBBBB

File2:(key: column 1-13)
AAAAAAAAAAAAA
CCCCCCCCCCCCC
DDDDDDDDDDDDD

I'm using the below code but only gets this output:
AAAAAAAAAAAAA
CCCCCCCCCCCCC
DDDDDDDDDDDDD

I have submitted a test job with the exact same SYSIN you have specified above and the same sample records.
I then submitted a second test job with your sample records, but I simplified the SYSIN statements as follows:

Both jobs got the following results:

Please send me your complete job listing offline at alissa.margulies@syncsort.com if you require further assistance.

Thanks Alissa your code had worked mine didn't. Thanks a bunch!