Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Matching Records using Dfsort

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Megalai

New User


Joined: 13 Jan 2006
Posts: 16

PostPosted: Thu Feb 02, 2006 6:23 pm    Post subject: Matching Records using Dfsort
Reply with quote

Hi Friends,

Please tell me the Solution for the below.

Input file1:

Cust id Address
1 A1
2 A1
3 A1
2 A2

Input file 2:

Custid Contact
1 C1
1 C2
1 C3
2 C1
2 C2


Output file should be like this:

Cust id Address Contact
1 A1 C1
1 A1 C2
1 A1 C3
2 A1 C1
2 A2 C1
2 A1 C2
2 A2 C2
3 A1


i.e) for the matching custid's the address & contact info should be written to the output file along with the cust id.


Thanks and Regards,
Megalai.
Back to top
View user's profile Send private message

Megalai

New User


Joined: 13 Jan 2006
Posts: 16

PostPosted: Thu Feb 02, 2006 6:30 pm    Post subject: Re: Matching Records using Dfsort
Reply with quote

Hi Friends,

Please assume that the Record length is 80.

Thanks,
Megalai .
Back to top
View user's profile Send private message
fixdoubts

New User


Joined: 21 Oct 2005
Posts: 54

PostPosted: Thu Feb 02, 2006 7:22 pm    Post subject:
Reply with quote

Hi,

This might help you

Code:

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG   DD SYSOUT=*   
//DFSMSG    DD SYSOUT=*   
//FILEIN1   DD *           
1 A1                       
2 A1                       
3 A1                       
2 A2                       
//FILEIN2   DD *           
1 C1                       
1 C2                       
1 C3                       
2 C1                       
2 C2                                           
//OUT   DD SYSOUT=*                             
//T1   DD DSN=XT2.SMS.TEST.SORT3,DISP=MOD       
//TOOLIN   DD *                                 
    COPY FROM(FILEIN1) TO(T1) USING(CPY1)       
    COPY FROM(FILEIN2) TO(T1) USING(CPY2)       
    SPLICE FROM(T1) TO(OUT) ON(1,1,CH)-         
     WITHALL KEEPNODUPS WITH(06,02) USING(CPY3)
/*                                             
//CPY1CNTL DD *                                 
    OUTREC FIELDS=(1:1,4,80:X)                 
/*                                             
//CPY2CNTL DD *                                 
    OUTREC FIELDS=(1:1,1,6:3,2,80:X)           
/*                                             
//CPY3CNTL DD *                                 
    OUTREC FIELDS=(1:1,1,6:3,2,80:X)   
/*                                     
//CPY3CNTL DD *                       
   OUTFIL FNAMES=OUT,OUTREC=(1,80)     
/*   
//   



The out put will be like this

Code:

1 A1 C1   
1 A1 C2   
1 A1 C3   
2 A1       
2 A1 C1   
2 A1 C2   
3 A1       


Regards,
Back to top
View user's profile Send private message
Megalai

New User


Joined: 13 Jan 2006
Posts: 16

PostPosted: Thu Feb 02, 2006 8:04 pm    Post subject: Re: Matching Records using Dfsort
Reply with quote

Hi Fixdoubts,

Thanks for ur Reply. But my Requirement is i have to get the matching records for each Cust id the address and Contact.

Thanks,
Megalai
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Feb 02, 2006 10:31 pm    Post subject:
Reply with quote

I think what you're trying to do is the equivalent of a cartesian join - the first 2 in file1 has to be joined with the first and second 2 in file2, and then the second 2 in file1 has to be joined with the first and second 2 in file2. DFSORT/ICETOOL cannot do that.
Back to top
View user's profile Send private message
Megalai

New User


Joined: 13 Jan 2006
Posts: 16

PostPosted: Tue Feb 07, 2006 3:01 pm    Post subject: Re: Matching Records using Dfsort
Reply with quote

Hi Frank,

Thanks for ur Reply.

thanks and Regards,
Megalai.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Nov 25, 2009 4:36 am    Post subject:
Reply with quote

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), you can now do a Cartesian join with JOINKEYS. For your example, you could use a DFSORT job like this:

Code:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//IN1 DD *
1             A1
2             A1
3             A1
2             A2
//IN2 DD *
1             C1
1             C2
1             C3
2             C1
2             C2
//SORTOUT DD SYSOUT=*
//SYSIN    DD    *
  JOINKEYS F1=IN1,FIELDS=(1,1,A)
  JOINKEYS F2=IN2,FIELDS=(1,1,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,31,F2:15,2)
  OPTION COPY
/*


For complete details on JOINKEYS and the other new functions available with the Nov, 2009 DFSORT PTF, see:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us