Tags

, , , , , ,

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)
/*
You might also like to look at:
SORT – CONVERT PD to ZD and BI to ZD
SORT – EDIT FIRST AND LAST RECORD
SORT – EDIT MASK PATTERNS
SORT – EDIT NUMERIC FIELDS (I,T,S)
SORT – FINDREP, IFTHEN and INREC
SORT – How to use VB dataset (VLSHRT)
SORT – INCLUDE and OMIT
SORT – INREC, INCLUDE and OMIT
SORT – JUSTIFY, SQUEEZE and OVERLAY
SORT – LOOKUP AND CHANGE
SORT – OUTPUT DATASET DCB (LRECL,RECFM,BLKSIZE)
SORT – OUTREC – INSERT ZEROES, BLANKS, STRINGS
SORT – OUTREC – TRAN
SORT – PARSE – REFORMAT VARIABLE FIELDS
SORT – Process Order of CONTROL Statements
SORT – REMOVE DUPLICATES AND WRITE THEM TO XSUM
SORT – SET RETURN CODE ‘04’ IF OUTPUT FILE IS EMPTY
SORT – SKIPREC and STOPAFT
SORT – SUM FIELDS
SORT – TO REPLACE A CHAR WITH ANOTHER CHAR – ALTSEQ
SORT – INREC
SORT – SYMBOLS
SORT JOB – SIMPLE COPY
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
SORT JOIN – UNPAIRED RECORDS INDICATOR
Advertisements