SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
We can make use of SORT
to join two files and writes records from both files
1. Both Files Matching
Records (Inner Join)
Write only records that
match on key from both files.
//STEP01 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=INPUT.FILE.ONE,DISP=SHR
//SORTJNF2 DD DSN=INPUT.FILE.TWO,DISP=SHR
//SORTOUT DD DSN=OUTPUT.FILE,
// DISP=(NEW,CATLG,DELETE),
//
SPACE=(CYL,(100,200),RLSE),
//
DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS
FILES=F1,FIELDS=(1,10,A)
JOINKEYS
FILES=F2,FIELDS=(1,10,A)
REFORMAT
FIELDS=(F1:1,71,F2:1,9)
/*
Here both the files have
keys at 1-10. And only the matching records are written
into output file.
REFORMAT fields tells
the sort on what fields to be written into output file from the input files.
2. Both Files Matching
Records + Non Matching from File1 (Left outer join)
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS
FILES=F1,FIELDS=(1,10,A)
JOINKEYS
FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1
REFORMAT
FIELDS=(F1:1,71,F2:1,9)
/*
3. Non Matching from
File1
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS
FILES=F1,FIELDS=(1,10,A)
JOINKEYS
FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT
FIELDS=(F1:1,71,F2:1,9)
/*
4. Both Files Matching
Records + Non Matching from File2(Right outer join)
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS
FILES=F1,FIELDS=(1,10,A)
JOINKEYS
FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F2
REFORMAT
FIELDS=(F1:1,71,F2:1,9)
/*
5. Non Matching from
File2
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS
FILES=F1,FIELDS=(1,10,A)
JOINKEYS
FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F2,ONLY
REFORMAT FIELDS=(F1:1,71,F2:1,9)
/*
6. Both Files Matching
Records + Non Matching from both files
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS
FILES=F1,FIELDS=(1,10,A)
JOINKEYS
FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1,F2
REFORMAT
FIELDS=(F1:1,71,F2:1,9)
/*