IBM Mainframe Forum

[Mann_B]

Hi All

Can we do Join Pair and Unpair on different key flds in single Sort step ?
I have 2 files ,file1 & file2 .I need to Pair 2 files for the based on 2 different key fields .The O/p should have record from 2nd file which is matched on first field (fld1) and not matched on second field(fld2).

File 1:
fld 1 fld2
---------------
AAAA ZZZZ
BBBBB YYYY
CCCC XXXXX

File 2:
fld 1 fld2
---------------
AAAA KKKK
BBBBB YYYY
DDDD XXXXX

If I have to match on first and second field,My output should be AAAA KKKK (from FIle 2).For the first record in file2,the 2 files match on first field but not matched on second field.

I have searched and I could see we have different syntax for Pair and Unpair on same key field.But I could n ot find it on different key field.
Can you please let me know if this is possible using joins?

[BillyBoyo]

The key for JOINKEYS does not have to be in the same position on the two files.

I'm not sure if this answers your question, so if it doesn't please do a better job of explaining, with a sample expected output for your given inputs.

[NicC]

Try posting in the correct section. This is JCL. If you use DFSORT you should have posted there , SYNCSORT then the SYNCSORT section. Tell us which and the post will be moved to the correct section.

[Mann_B]

HI NicC

Thank you.We use DFSORT..

[Mann_B]

HI BillyBoyo

Thank you for the reply.
"The key for JOINKEYS does not have to be in the same position on the two files".

I have tested JOINKEYS with different positions on 2 files.My Query here is different.I need to Pair on 2 key fields.
Consider below are 2 files.

File 1:
fld 1 fld2
(Pos1-4) (Pos9-4)
--------------
AAAA ZZZZ
BBBBB YYYY
CCCC XXXXX

File 2:
fld 1 fld2
(Pos1-4) (Pos9-4)
--------------
AAAA KKKK
BBBBB YYYY
DDDD XXXXX

The 2 files should match on fld1 and should not match on fld2.
My o/p after pairing should be AAAA KKKK (from file 2).fld1 from file1 and file 2 is AAAA which is same in both files,where in fld 2 is different.
I have tested Pairing based on more than one field and from different positions.But I am not sure if we can do such pair where Match based on one field and if the record is matched on fld 1 ,chk if it matches on fld2.If it matched on fld1 and unmatches on fld2 only then write to out file.

We can perform this in 2 steps.
1)Pair based on fld1:

Select all

JOINKEYS F1=INA,FIELDS=(1,4,A)                             
JOINKEYS F2=INB,FIELDS=(1,4,A)
REFORMAT FIELDS=(F1:1,80)

The paired records from File1 need to be matched again on fld2.

2)Unpair based on fld2:
F1 here is matched out file from Previous step which will have matched records on fld1 from File1.

Select all

JOINKEYS F1=INA,FIELDS=(9,4,A)                             
JOINKEYS F2=INB,FIELDS=(9,4,A)
JOIN UNPAIRED,F2,ONLY

This gives record from File 2 which is having match on fld1 from both files(from step1) and unmatch on fld 2.

But this is like reading same set of files twice.So can we perform the matchign from 2 steps in a Single step?

Please let me know if more details are required.

[BillyBoyo]

I think you have a potential for "false hits" with your two-step approach, if the second key is not unique across the file.

If you do the JOINKEYS on the first key, you can use IFTHEN=(WHEN=(logicalexpression) to test the values in the second key position, and then arrange, via a "flag" and OUTFIL OMIT/INCLUDE to only write the records where you have a match, and the second key is unmatched.

[skolusu]

Mann_B,

Use the following DFSORT JCL which will give you the desired results.

Select all

//STEP0100 EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//INA      DD *                                         
----+----1----+----2----+----3----+----4----+----5----+-
AAAA    ZZZZ                                           
BBBB    YYYY                                           
CCCC    XXXX                                           
//INB      DD *                                         
AAAA    KKKK                                           
BBBBB   YYYY                                           
DDDD    XXXX                                           
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=INA,FIELDS=(1,4,A)                       
  JOINKEYS F2=INB,FIELDS=(1,4,A)                       
  JOIN UNPAIRED,F2                                     
  REFORMAT FIELDS=(F2:1,80,?,F1:9,4)                   
  OUTFIL BUILD=(1,80),                                 
  INCLUDE=(81,1,CH,EQ,C'B',AND,(9,4,CH,NE,82,4,CH),OR, 
          (81,1,CH,EQ,C'2'))                           
//*

The output from this job is

Select all

AAAA    KKKK 
DDDD    XXXX 

[Mann_B]

HI BillyBoyo

Can you please tell me syntax for IFTHEN=(WHEN=(logicalexpression) synatx for unmatching on second key. I have searched but could not get how exactly we can use it on a record after Pairing on first key.

[BillyBoyo]

Kolusu's solution is better. Did you try it? Puts the "IF" together with the INCLUDE, just by using INCLUDED instead of what I suggested.

If you search/browse the forum you'll find many examples of IFTHEN. Look at the DFSORT Getting Started as a good way to... get started.

[Mann_B]

Thanks BillyBoyo .
Hi skolusu

As mentioned the oupt should be only AAAA KKKK (from file 2 - match on fld 2 and unmatch on fld2).I tried to sub the JCL you have given .But I am getting errors.

Can you please let me what exaclty is
REFORMAT FIELDS=(F2:1,80,?,F1:9,4)

INCLUDE=(81,1,CH,EQ,C'B',AND,(9,4,CH,NE,82,4,CH),OR,
(81,1,CH,EQ,C'2'))

My jobs is saying "SORTOUT INCLUDE/OMIT FIELD BEYOND RECORD ".

Thank you.