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)
/*

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=(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)
/*

If you would like to Earn Free Stocks, Credit card Points and Bank account Bonuses, Please visit My Finance Blog

You may also like to look at:

REXX TOOL # 01 – O – Open Any Mainframe Element from any ISPF Screen

CA-Easytrieve/Plus – Basics

Important SQL CODES and ABEND CODES

SORT – Alphanumeric Tests for INCLUDE and OMIT

SORT – EQUALS Option

SORT – PARSE – REFORMAT VARIABLE FIELDS

SORT – FINDREP, IFTHEN and INREC

SORT – JUSTIFY, SQUEEZE and OVERLAY

SORT – LOOKUP AND CHANGE

SORT – CONVERT PD to ZD and BI to ZD

SORT – OUTREC – TRAN

SORT – OUTREC – INSERT ZEROES, BLANKS, STRINGS

SORT – OUTPUT DATASET DCB (LRECL,RECFM,BLKSIZE)

SORT – INCLUDE and OMIT

SORT – Process Order of CONTROL Statements

SORT – How to use VB dataset (VLSHRT)

SORT – EDIT MASK PATTERNS

SORT – EDIT NUMERIC FIELDS (I,T,S)

SORT – EDIT FIRST AND LAST RECORD

SORT – SYMBOLS

SORT – TO REPLACE A CHAR WITH ANOTHER CHAR – ALTSEQ

SORT – SET RETURN CODE ‘04’ IF OUTPUT FILE IS EMPTY

SORT – INREC, INCLUDE and OMIT

SORT – SKIPREC and STOPAFT

SORT – INREC

SORT – SUM FIELDS

SORT – REMOVE DUPLICATES AND WRITE THEM TO XSUM

DFSORT – Informational and Error Messages

SORT JOIN – UNPAIRED RECORDS INDICATOR

SORT JOIN – TO JOIN TWO FILES BASED ON A KEY

SORT JOB – SIMPLE COPY

SORT TIPS