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
 

 

SYNCSORT Match columns in first file and rows of second file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Geetha B

New User


Joined: 24 Nov 2007
Posts: 12
Location: chennai

PostPosted: Mon Jun 16, 2008 2:30 pm    Post subject: SYNCSORT Match columns in first file and rows of second file
Reply with quote

Hi

I have 2 files.The first file contains only one record,in each column of that record i would be having the custid field.

In the 2nd file i would be having the records for custid like custid,name,description

File 1

100 200 300 400

File 2

100 abc
200 ade
350 ref
800 retag

my output should be

100 abc
200 ad

There must be one to one match between the first column custid and the first row cust id,similarly the second column custid must be matched with the 2nd row custid

Only the matched records must be written to the output file

The number of columns in the first file is directly proportional to the number of rows in the second file

The custid field is of length 3 and they are delimited by spaces in the first file

Can it be done using sort?
Back to top
View user's profile Send private message

HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Mon Jun 16, 2008 3:32 pm    Post subject: Reply to: Match between columns in first file and rows of se
Reply with quote

I guess it can be done
but If my understand is right, if you have only custid's in first file and 2nd file holds custid's and other details..then your output should be custid's with details..then can't we use directly the second file?

If you want only the details which custid's present on first file yes, then does that mean the number of custid's are different to second & first file?
Back to top
View user's profile Send private message
Geetha B

New User


Joined: 24 Nov 2007
Posts: 12
Location: chennai

PostPosted: Mon Jun 16, 2008 3:49 pm    Post subject: Reply to: Match between columns in first file and rows of se
Reply with quote

The first file is a master file it contains only the valid cusid's.

I need to fetch only those custid details from the second file,which are present in the first file
Back to top
View user's profile Send private message
tangentray

New User


Joined: 30 Dec 2006
Posts: 20
Location: Kolkata, India

PostPosted: Mon Jun 16, 2008 6:01 pm    Post subject:
Reply with quote

This might be more complicated than it looks. Most tools on mainframes work row-wise, that is one row at a time. Your problem is simplified many-fold if you can get the first dataset transposed, i.e. say it is like:
100
200
300
400

Then you can do a MERGE FEILDS and get your result.

But I am not aware of any tool which can directly transpose a dataset in such fashion. To come up with a solution using existing tools will be complicated, time consuming and would probably have a lot of limitations. Try using any other technology that is available with you to do this, I bet it will be simpler.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 16, 2008 8:47 pm    Post subject:
Reply with quote

Hello,

How is file1 created? What happens if there are more custid's than can fit in one record?

If this is something new and is still being tested, it may be to your advantage to change how file1 is created and put only 1 custid per record rather than some series of them.
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Mon Jun 16, 2008 9:23 pm    Post subject:
Reply with quote

Depending on how many fields are present in the master file, this may be a viable option:
Code:
//SORT     EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                             
100 200 300 400 500 600 700 800 900 150 250 350 450 550 650 
//SORTOUT  DD DISP=(NEW,PASS),DSN=&TEMP                     
//SYSIN    DD *                                             
  SORT FIELDS=COPY                                           
  OUTFIL FILES=OUT,               
  OUTREC=(1,3,/,                 
          5,3,/,                 
          9,3,/,                 
         13,3,/,                 
         17,3,/,                 
         21,3,/,                 
         25,3,/,                 
         29,3,/,                 
         33,3,/,                 
         37,3,/,                 
         41,3,/,                 
         45,3,/,                 
         49,3,/,                 
         53,3,/,                 
         57,3)                   
/*                               
//SORT     EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//SORTJNF1 DD DISP=(OLD,DELETE),DSN=&TEMP     
//SORTJNF2 DD *                               
100 ABC                                       
110 DEF                                       
200 GHI                                       
321 JKL                                       
450 MNO                                       
778 PQR                                       
//SORTOUT  DD SYSOUT=*                         
//SYSIN    DD *                               
  JOINKEYS FILES=F1,FIELDS=(1,3,A)             
  JOINKEYS FILES=F2,FIELDS=(1,3,A)             
  REFORMAT FIELDS=(F2:1,7)                     
  SORT FIELDS=COPY                             
/*

The following output is produced:
Code:
100 ABC
200 GHI
450 MNO
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am


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