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 

Many to many matching of record

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


Joined: 26 Dec 2007
Posts: 4
Topics: 3
PostPosted: Thu Aug 25, 2011 5:23 am    Post subject: Many to many matching of record Reply with quote
Hi,

I have a requirement where i need to merge every record from file1 with every record in file2. Can it be done using DFSORT?

The layout of files:
File 1:
--------
name x(6)

File2:
-------
value x(10)
alternate-val x(10)

input:
--------
FILE 1
-----------
RAMA
SAAM
RAAJA

FILE 2
------------
Y 999-999999
OBSOLETE 010-201030

OUTPUT:
------------
RAMA Y 999-999999
SAAM Y 999-999999
RAAJA Y 999-999999
RAMA OBSOLETE 010-201030
SAAM OBSOLETE 010-201030
RAAJA OBSOLETE 010-201030
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Thu Aug 25, 2011 7:32 am    Post subject: Reply with quote
nithyakothai,
See if below helps...Your layout for file2 is not clear!!!
Code:
//STEP0001     EXEC PGM=SORT         
//SYSOUT   DD SYSOUT=*               
//SORTJNF1 DD *                       
RAMA                                 
SAAM                                 
RAAJA                                 
//SORTJNF2 DD *                       
Y 999-999999                         
OBSOLETE 010-201030                   
//SORTOUT  DD SYSOUT=*               
//SYSIN    DD *                       
  JOINKEYS FILES=F1,FIELDS=(07,01,A),SORTED,NOSEQCK 
  JOINKEYS FILES=F2,FIELDS=(21,01,A),SORTED,NOSEQCK   
  REFORMAT FIELDS=(F1:01,06,F2:01,20)
  SORT FIELDS=(07,20,CH,D),EQUALS     
/*                                   
//JNF1CNTL DD *                       
 INREC OVERLAY=(07:C'1')             
//*                                   
//JNF2CNTL DD *                       
 INREC OVERLAY=(21:C'1')             
//*

OUTPUT
Code:
RAMA  Y 999-999999       
SAAM  Y 999-999999       
RAAJA Y 999-999999       
RAMA  OBSOLETE 010-201030
SAAM  OBSOLETE 010-201030
RAAJA OBSOLETE 010-201030

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Thu Aug 25, 2011 12:31 pm    Post subject: Reply with quote
Actually, we can be a little more clever and use a COPY for the main task rather than a SORT as follows:

Code:

//STEP0001 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//IN1 DD *                                                   
RAMA                                                         
SAAM                                                         
RAAJA                                                         
//IN2 DD *                                                   
Y 999-999999                                                 
OBSOLETE 010-201030                                           
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  JOINKEYS F1=IN2,FIELDS=(21,1,A),SORTED,NOSEQCK             
  JOINKEYS F2=IN1,FIELDS=(7,1,A),SORTED,NOSEQCK               
  REFORMAT FIELDS=(F2:1,6,F1:1,20)                           
  OPTION COPY                                                 
/*                                                           
//JNF1CNTL DD *                                               
  INREC OVERLAY=(21:C'1')                                     
//JNF2CNTL DD *                                               
  INREC OVERLAY=(7:C'1')                                     
/*

_________________
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
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