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 

SORT - matching and non-matching records

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


Joined: 01 Jun 2003
Posts: 372
Topics: 105
PostPosted: Fri Sep 17, 2010 8:57 am    Post subject: SORT - matching and non-matching records Reply with quote
Hi,

Input File-1: LRECL=80, RECFM=FB
Code:

----+
0203N
1294N
9456N


Input File-2: LRECL=80, RECFM=FB
Code:

----+----1----+----2
Header Record 0203
1----- ------ 0203
2----- ------ 0203
3----- ------ 0203

----+----1----+----2
Header Record 3648
1----- ------ 3648
2----- ------ 3648
3----- ------ 3648


I would like to match above two files and matched records from Input File-2 should go to MATCH and all other from Input File-2 should go to UNMATCH files. I am doing the comparison between keys of columns 1,4 (file-1) and columns 15,4 (file-2).

I tried JOINKEYS with JOIN UNPAIRED and REFORMAT FIELDS=(F2:1,80) but it is populating MATCH file only and UNMATCH file is never created.

Please help.

Thanks.
_________________
MF
==
Any training that does not include the emotions, mind and body is incomplete; knowledge fades without feeling.
==
Back to top
View user's profile Send private message Send e-mail
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Fri Sep 17, 2010 9:28 am    Post subject: Reply with quote
mf_user,
Since you want both matched and unmatched records, you will have to use JOIN UNPAIRED,F2 which gives you UNPAIRED records from F2 as well as PAIRED records (Right outer join). Now, that you also want records to be written to different output files, you would have to use DFSort's 1 byte join indicator('?') to determine which records match and which ones doesn't and route them to different output files accordingly.

'B' - in the join indicator indicates that the key was found in F1 and F2.
'1' - in the join indicator indicates that the key was found in F1, but not in F2.
'2' - in the join indicator indicates that the key was found in F2, but not in F1.

Try below job...
Code:
//STEP01 EXEC PGM=SORT                                 
//SYSOUT DD SYSOUT=*                                   
//SORTJNF1 DD *                                         
0203N                                                   
1294N                                                   
9456N                                                   
//SORTJNF2 DD *                                         
HEADER RECORD 0203                                     
1----- ------ 0203                                     
2----- ------ 0203                                     
3----- ------ 0203                                     
----+----1----+----2                                   
HEADER RECORD 3648                                     
1----- ------ 3648                                     
2----- ------ 3648                                     
3----- ------ 3648                                     
//MATCH   DD SYSOUT=*                                   
//UNMATCH DD SYSOUT=*                                   
//SYSIN DD *                                           
  JOINKEYS FILE=F1,FIELDS=(01,04,A)                     
  JOINKEYS FILE=F2,FIELDS=(15,04,A)                     
  JOIN UNPAIRED,F2                                     
  REFORMAT FIELDS=(F2:1,80,?)                           
  SORT FIELDS=COPY                                     
  OUTFIL FNAMES=MATCH,INCLUDE=(81,1,CH,EQ,C'B')         
  OUTFIL FNAMES=UNMATCH,INCLUDE=(81,1,CH,EQ,C'2')       
/*                                                     

Thanks,
Back to top
View user's profile Send private message
mf_user
Intermediate


Joined: 01 Jun 2003
Posts: 372
Topics: 105
PostPosted: Fri Sep 17, 2010 9:36 am    Post subject: Reply with quote
Hi,

I will try this job and come back.

Thanks.
_________________
MF
==
Any training that does not include the emotions, mind and body is incomplete; knowledge fades without feeling.
==
Back to top
View user's profile Send private message Send e-mail
mf_user
Intermediate


Joined: 01 Jun 2003
Posts: 372
Topics: 105
PostPosted: Fri Sep 17, 2010 9:39 am    Post subject: Reply with quote
Hi,

I tried this and got U0016 abend because of syntax error in REFORMAT statement !!

Thanks.
_________________
MF
==
Any training that does not include the emotions, mind and body is incomplete; knowledge fades without feeling.
==
Back to top
View user's profile Send private message Send e-mail
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Sep 17, 2010 10:08 am    Post subject: Reply with quote
Then I'd guess you're using Syncsort which doesn't support the ? indicator instead of DFSORT which does. Check your messages (ICE for DFSORT or WER for Syncsort).
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Fri Sep 17, 2010 10:09 am    Post subject: Reply with quote
MF,
If its not too much of a trouble, could you please post your entire sysout messages? I am asking this because when I ran the job, it ran successfully.

Thanks,
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