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
 

 

one to many merge.

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

New User


Joined: 24 Aug 2012
Posts: 5
Location: canada

PostPosted: Sat Aug 25, 2012 1:53 am    Post subject: one to many merge.
Reply with quote

I have two files. A one to many relationship. I need to have an merge the files so that the output file has one record with the record for the 'many' concatdacted.

Example
File a
1 robert
2 charly
3 harry

File b
1 rose
1 blue
1 green
3 black

Output
1 robert rose blue green
3 harry black


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

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Sat Aug 25, 2012 2:49 am    Post subject:
Reply with quote

robert, galambos,

Please ALL of the following questions

1. What is the LRECL and RECFM of File1?
2. What is the LRECL and RECFM of File2?
3. What is the position,length and format of the key in file1?
4. What is the position,length and format of the key in file2?
5. What is the position, length and format of the name field you want to merge in file1?
6. What is the position, length and format of the name field you want to merge in file2?
7. What is the max number of duplicates you expect for each key?
8. Last but not least , please run the following job and show us the entire sysout so that we can determine the level of DFSORT your shop has
Code:

//S1 EXEC PGM=ICEMAN
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*
Back to top
View user's profile Send private message
robert, galambos

New User


Joined: 24 Aug 2012
Posts: 5
Location: canada

PostPosted: Sat Aug 25, 2012 3:13 am    Post subject:
Reply with quote

1) LrecL 96 fB
2) LrecL. 85 VB
3) 22 PS lenght 13
4) 49 PS lenght 13
5) (1,6,Z),(22,13,BI),(40,10,c)
6) (53,2,C)
7) 6 records max fields.

I can say that there is ICEman at the site but cAn only provide the information on tuesday as its a holiday in the UK on monday


File 1 repersents customer record
File two reoersents type of accounts the customer has

Thanks for your help
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Sat Aug 25, 2012 3:35 am    Post subject: Reply to: one to many merge.
Reply with quote

robert, galambos wrote:
1) LrecL 96 fB
2) LrecL. 85 VB
3) 22 PS lenght 13
4) 49 PS lenght 13
5) (1,6,Z),(22,13,BI),(40,10,c)
6) (53,2,C)
7) 6 records max fields.

I can say that there is ICEman at the site but cAn only provide the information on tuesday as its a holiday in the UK on monday


File 1 repersents customer record
File two reoersents type of accounts the customer has

Thanks for your help


robert, galambos,

Something doesn't make sense here. You said the FILE 2 Key position is 49 PS length 13 and the position, length and format of the name field you want to merge in file2 is 53,2,C

They are overlapping fields. Please post the correct positions and also sample data from both input files and desired output.

Do you have duplicates in File1?
Back to top
View user's profile Send private message
robert, galambos

New User


Joined: 24 Aug 2012
Posts: 5
Location: canada

PostPosted: Sat Aug 25, 2012 11:36 am    Post subject:
Reply with quote

SOrry. It should be 73 and not 53.

And there are no duplicates in file one. Only one customer record/number. The key on both files

File 1
Code:
0001 aaaaa
0003 bbbbb
0006 cccccc

File 2
Code:
0001 vv
0001 ff
0001 gg
0006 vv


Output
Code:
0001 aaaaa vv ff gg
0006 cccccc vv

One to many
No dups in file 1
If no records in file 2 then no output record

For every file one, customer record, will have at least one record in file 2 (account type record) however not the other way around. You can not have a account type record without an customer record. And this is the type of records I am working on.

It does not need to be exact as I can take the ball and run with it. But an idea/direction would be great.

Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Aug 27, 2012 3:06 pm    Post subject: Reply to: one to many merge.
Reply with quote

robert, galambos,

Can you review my "tidying" to see that everything is included?

Can you resolve the

Quote:
If no records in file 2 then no output record


vs

Quote:
For every file one, customer record, will have at least one record in file 2
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Aug 27, 2012 3:53 pm    Post subject: Reply to: one to many merge.
Reply with quote

for ideas on how to proceed see
http://www.ibmmainframes.com/viewtopic.php?t=57507&highlight=splice
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Aug 27, 2012 3:55 pm    Post subject: Reply to: one to many merge.
Reply with quote

An idea, enrico, suitable if JOINKEYS is not supported. Which we don't know until tomorrow.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Aug 27, 2012 10:43 pm    Post subject:
Reply with quote

Use the following DFSORT JCL which will give you the desired results. Since your file2 is a VB file , you need to accound the RDW making the positions you mentioned 4 bytes greater. So the key in VB file actually starts in position 53 and the values you want are in position 77.


Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//INA      DD DSN=Your Input VB 85 byte file,DISP=SHR
//INB      DD DSN=Your Input FB 96 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(53,13,A)                                   
  JOINKEYS F2=INB,FIELDS=(22,13,A)                                   
  REFORMAT FIELDS=(F2:1,6,22,13,40,10,F1:77,2)                       
                                                                     
  INREC IFOUTLEN=41,IFTHEN=(WHEN=INIT,BUILD=(1,29,12X,30,2)),       
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(7,13),PUSH=(44:SEQ=1)),               
  IFTHEN=(WHEN=GROUP,BEGIN=(44,1,ZD,EQ,1),PUSH=(30:42,2),RECORDS=6),
  IFTHEN=(WHEN=GROUP,BEGIN=(44,1,ZD,EQ,2),PUSH=(32:42,2),RECORDS=5),
  IFTHEN=(WHEN=GROUP,BEGIN=(44,1,ZD,EQ,3),PUSH=(34:42,2),RECORDS=4),
  IFTHEN=(WHEN=GROUP,BEGIN=(44,1,ZD,EQ,4),PUSH=(36:42,2),RECORDS=3),
  IFTHEN=(WHEN=GROUP,BEGIN=(44,1,ZD,EQ,5),PUSH=(38:42,2),RECORDS=2),
  IFTHEN=(WHEN=(44,1,ZD,EQ,1),OVERLAY=(32:10X)),                     
  IFTHEN=(WHEN=(44,1,ZD,EQ,2),OVERLAY=(34:08X)),                     
  IFTHEN=(WHEN=(44,1,ZD,EQ,3),OVERLAY=(36:06X)),                     
  IFTHEN=(WHEN=(44,1,ZD,EQ,4),OVERLAY=(38:04X)),                     
  IFTHEN=(WHEN=(44,1,ZD,EQ,5),OVERLAY=(40:02X)),                     
  IFTHEN=(WHEN=(44,1,ZD,EQ,6),OVERLAY=(40:42,2))                     
                                                                     
  OUTFIL REMOVECC,NODETAIL,                                         
  SECTIONS=(7,13,TRAILER3=(1,41))                                   
//*
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 SQL Merge error -254 Robin Sulsona DB2 8 Wed Jun 01, 2016 8:28 pm
No new posts SAS MERGE to DFSORT Musab Ahmed DFSORT/ICETOOL 6 Mon Feb 01, 2016 5:44 pm
No new posts Merge two files having two trailer re... R.Manivannan DFSORT/ICETOOL 7 Wed Nov 25, 2015 9:18 pm
No new posts Merge two files with some replace/ove... dheeraj.ramchandani DFSORT/ICETOOL 10 Thu Sep 03, 2015 7:59 pm
No new posts Merge multiple records into single re... mkk157 DFSORT/ICETOOL 4 Wed May 27, 2015 5:43 pm


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