MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Need Help with Join Keys

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
rgcmohan
Beginner


Joined: 30 Jun 2017
Posts: 5
Topics: 3

PostPosted: Thu Nov 16, 2017 5:47 am    Post subject: Need Help with Join Keys Reply with quote

Description:

We need to extract the records from the F1 file only, pairing the values in column 1,5 and get the unpaired values.

For example we have 11111 – 3 rows in file1 and 2 rows in file2 as shown below:
Code:

File 1               File2
11111 HEN     11111 RAT            - eliminate                                         
11111 RAT     11111 MAT            - eliminate                                                                                 
11111 MAT                          - write to output file   

We tried the below Sortcard and it didn’t get the desired results.

Sortcard used:

Code:

//STEP02 EXEC PGM=SORT                                       
//SYSOUT DD SYSOUT=*                                         
//SORTJNF1 DD *                                               
11111 HEN                                                     
22222 DOG                                                     
33333 COW                                                     
11111 RAT                                                     
33333 MAN                                                     
11111 MAT                                                     
/*                                                           
//SORTJNF2 DD *                                               
33333 COW                                                     
11111 RAT                                                     
11111 MAT                                                     
/*                                                           
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  JOINKEYS FILE=F1,FIELDS=(1,5,A) INCLUDE=(11,4,ZD,GT,0001)   
  JOINKEYS FILE=F2,FIELDS=(1,5,A)                             
  JOIN UNPAIRED,F1             
  REFORMAT FIELDS=(F1:1,19,F2:1,9),FILL=C'#'               
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(16:SEQ=4)) 
  SORT FIELDS=(11,4,ZD,A)                                   
  OUTFIL INCLUDE=(16,4,ZD,GT,0001,OR,20,1,CH,EQ,C'#'),     
  OUTREC=(1,10)                                             
  END                                                       
/*                                                         
//JNF1CNTL DD *                                             
  INREC OVERLAY=(11:SEQNUM,4,ZD)                           
/*       

 


Output of the above sort card:

Code:


11111 HEN
22222 DOG
11111 RAT
11111 RAT
33333 MAN
11111 MAT
11111 MAT


Expected output:

Code:


22222 DOG   
33333 MAN
11111 MAT

Back to top
View user's profile Send private message
Terry_Heinze
Supermod


Joined: 31 May 2004
Posts: 391
Topics: 4
Location: Richfield, MN, USA

PostPosted: Thu Nov 16, 2017 9:25 am    Post subject: Reply with quote

Please learn how to use the Code tags. Check this link as to how to use CODE tags

https://www.mvsforums.com/helpboards/viewtopic.php?p=19031#19031
_________________
....Terry
Back to top
View user's profile Send private message Send e-mail
rgcmohan
Beginner


Joined: 30 Jun 2017
Posts: 5
Topics: 3

PostPosted: Thu Nov 16, 2017 10:02 am    Post subject: Thank you Terry Heinze Reply with quote

Thank you, code tags are not working for me for some reason.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Nov 16, 2017 10:05 am    Post subject: Re: Thank you Terry Heinze Reply with quote

rgcmohan wrote:
Thank you, code tags are not working for me for some reason.


rgcmohan,

You have disabled the BB code here. If you disable that your code tags would NOT work. Uncheck the radio button for the "Disable BB code in this post"


_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Nov 16, 2017 10:17 am    Post subject: Re: Need Help with Join Keys Reply with quote

rgcmohan wrote:


expected output

Code:


22222 DOG   
33333 MAN
11111 MAT



Your input does not match the output. How come 1111 MAT is selected when it had a match in file 2?

This is your input

Code:

11111 HEN          - HAS NO MATCH IN FILE2 -WRITE TO OUTPUT 
22222 DOG          - HAS NO MATCH IN FILE2 -WRITE TO OUTPUT 
33333 COW          - MATCHING RECORD - ELIMINATE             
11111 RAT          - MATCHING RECORD - ELIMINATE             
33333 MAN          - HAS NO MATCH IN FILE2 -WRITE TO OUTPUT 
11111 MAT          - MATCHING RECORD - ELIMINATE             
/*                 


Ideally the output would be

Code:

11111 HEN 
22222 DOG 
33333 MAN 

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
rgcmohan
Beginner


Joined: 30 Jun 2017
Posts: 5
Topics: 3

PostPosted: Thu Nov 16, 2017 12:54 pm    Post subject: Reply with quote

Thanks for your input,We are in a process of converting ViewDirect Infopac programs to Sort.

We need to acheive the output whatever the current infopac program provides.

Infopac program logic for comparison:

Code:


FILE DDNAME PREVDAY  INPUT FROM PREVDAY.$FILE_F(-1)  MATCHKEY UID 
FILE DDNAME CURRDAY  INPUT FROM CURRDAY.$FILE_F(-0)  MATCHKEY UID 
REPORT DDNAME SORTOUT SKELETON RPTSORT                             
IF EXIST('PREVDAY')=YES&EXIST('CURRDAYT')=NO                       
                                       
  STORE SORTOUT.UID     PREVDAY.UID                               
  STORE SORTOUT.XXX     PREVDAY.XXX                               
  STORE SORTOUT.YYYY    PREVDAY.YYYY                             
  STORE SORTOUT.NNN     PREVDAY.NNN                               
  STORE SORTOUT.RRR     PREVDAY.RRR                               
  STORE SORTOUT.CCC     PREVDAY.CCC                               
  STORE SORTOUT.TTT    'TEST'                                     
  WRITE DDNAME SORTOUT GROUP DL1                                   
                                                           
END IF                                                             
 




The first five column of file-1 will match with first five column of file-2.
It would not be bother about the 7,8,9 th column of file-1 or file-2.
for example:

Code:

11111 HEN first row of file-1 will match with 11111 RAT second row of file-2.
33333 COW third row of file-1 will match with 33333 COW first row of file-2.
11111 RAT fourth row of file-1 will match with 11111 MAT third row of file-2.


Better way to understand,I have modified the changes as below with ----> mark:

Code:

11111 HEN  - HAS NO MATCH IN FILE2 -WRITE TO OUTPUT ----> MATCHING WITH 11111 RAT- SECOND ROW OF FILE2
22222 DOG  - HAS NO MATCH IN FILE2 -WRITE TO OUTPUT ----> NO MATCHING WITH FILE2
33333 COW  - MATCHING RECORD - ELIMINATE            ----> MATCHING WITH 33333 COW -FIRST ROW OF FILE2
11111 RAT  - MATCHING RECORD - ELIMINATE            ----> MATCHING WITH 11111 MAT -THIRD RWO OF FILE2 
33333 MAN  - HAS NO MATCH IN FILE2 -WRITE TO OUTPUT ----> NO MATCHING WITH FILE2   
11111 MAT  - MATCHING RECORD - ELIMINATE            ----> NO MATCHING WITH FILE2


That is how the infopac does the comparison and provides the output for no matching :

Code:

22222 DOG                               
33333 MAN                                                     
11111 MAT 


Can you please help us to achive this?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Nov 16, 2017 2:23 pm    Post subject: Reply with quote

rgcmohan wrote:
Thanks for your input,We are in a process of converting ViewDirect Infopac programs to Sort.

We need to acheive the output whatever the current infopac program provides.

Infopac program logic for comparison:

Can you please help us to achive this?


rgcmohan,

1. Using JNF1CNTL build the File-1 Records with 10 byte and 4 byte sequence number

2. Using JNF2CNTL build the FILE-2 records with just the key (1, thru 5) and add a constant of C'0001'

3. In the same JNF2CNTL SUM FIELDS on the constant of 0001. This will give you a count as to how many duplicates are there in file2

4. Now on the Main task use Join Unpaired and have Reformat fields have the 14 byte from File-1 and the match indicator and the count from File-2

5. Use Inrec mark the keys (1 thru 5) a SEQNUM with Restart on Key(1,5) at the end (say position 22)

6. SORT on the JNF1CNTL seqnum which is at position 11 as you need to retain the original order of file-1 records

7. Using OUTFIL include any record which has a match indicator of "1" or if the Seqnum from step 5 is greater than COUNT field from file-2

8. You get the desired results.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Thu Nov 16, 2017 4:49 pm    Post subject: Reply with quote

Thank you Kolusu, Logic worked , below was the code we developed out of steps provided

Code:

 //STEP02 EXEC PGM=SORT         
 //SYSOUT DD SYSOUT=*           
 //SORTJNF1 DD *               
 11111 HEN                     
 22222 DOG                     
 33333 COW                     
 11111 RAT                     
 33333 MAN                     
 11111 MAT                     
 /*                             
//SORTJNF2 DD *                                             
33333 COW                                                   
11111 RAT                                                   
11111 MAT                                                   
/*                                                           
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                             
  JOINKEYS FILE=F1,FIELDS=(1,5,A)                           
  JOINKEYS FILE=F2,FIELDS=(1,5,A)                           
  JOIN UNPAIRED F1                                           
  REFORMAT FIELDS=(F1:1,14,F2:1,10)                         
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(26:SEQ=4))   
  SORT FIELDS=(11,4,ZD,A)                                   
  OUTFIL INCLUDE=(26,4,ZD,GT,21,4,ZD),                       
  OUTREC=(1,10)                                             
  END                                                       
/*                                                           
//JNF1CNTL DD *                                             
  INREC OVERLAY=(11:SEQNUM,4,ZD)                             
/*                                                           
//JNF2CNTL DD *       
   INREC BUILD=(1,5,X,C'0001')   
   SUM FIELDS=(7,4,ZD)           
 /*                             


Quote:


7. Using OUTFIL include any record which has a match indicator of "1"


Please explain on the above step, we did't get this part alone
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Nov 16, 2017 7:12 pm    Post subject: Reply with quote

Suchay wrote:
Thank you Kolusu, Logic worked , below was the code we developed out of steps provided

Please explain on the above step, we did't get this part alone


Suchay,

You need to handle unmatched records. The match indicator for them would be "1"

Code:

//SYSIN    DD *                                   
  JOINKEYS F1=INA,FIELDS=(1,5,A)                 
  JOINKEYS F2=INB,FIELDS=(1,5,A)                 
  JOIN UNPAIRED,F1                                 
  REFORMAT FIELDS=(F1:1,15,?,F2:7,4)             
  INREC OVERLAY=(22:SEQNUM,4,ZD,RESTART=(1,5))   

  SORT FIELDS=(11,4,CH,A)                         
                                                 
  OUTFIL BUILD=(1,10),                           
  INCLUDE=(16,1,CH,EQ,C'1',OR,                   
          (22,4,ZD,GT,17,4,ZD))                   
/*                                               
//JNF1CNTL DD *                                   
  INREC BUILD=(1,10,SEQNUM,4,ZD,X)               
/*                                               
//JNF2CNTL DD *                                   
  INREC BUILD=(1,5,X,C'0001')                     
  SUM FIELDS=(7,4,ZD)                             
/*                                               

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Fri Nov 17, 2017 11:42 am    Post subject: Reply with quote

Thank you Kolusu.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group