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
 

 

Merge files with key and duplicates

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

New User


Joined: 17 Nov 2010
Posts: 4
Location: amsterdam

PostPosted: Mon Dec 20, 2010 8:43 pm    Post subject: Merge files with key and duplicates
Reply with quote

Hi,

I know there are a lot of posts realted to file merging.. I tried to find my problem in first few pages resulted in the search list " for merge files" from this forum and couldnt find any similar!!

Could you please help? Thanks in advance. My request below:

I have input file #1 this way :

Code:
Name   City   Vehicle   Result

David Frankfurt         Bike   2
Frank      Rome   Bike   30
George   Rome   Car   4
Mark   Paris   Car   20


Input file #2

Code:
Name   City   Vehicle
Chris   Newyork   Taxi
David   Frankfurt   Bike
Frank   Rome   Bike
George   Rome   Car
Mark   Paris   Car
Mark   Paris   Car
Peter   Rome   Car

I want output to be :

Code:
Name   City   Vehicle   Result
Chris   Newyork   Taxi   
David   Frankfurt   Bike   2
Frank   Rome   Bike   30
George   Rome   Car   4
Mark   Paris   Car   20
Mark   Paris   Car   20
Peter   Rome   Car   


You could observe that:
(1) Both my inputs are sorted on name+city+vehicle ( my key) .
(2) Records of file #2 that are not found on file #1 will be written down as it is with result field empty.
(3) Records on fiel #2 that match with key name+city+vehicle in file #1 should have Result field populated from file #1.
(4) If there are duplicates on file 2, those duplicates must still be shown in output along with my point (3) here, if applicable.
(5) Every record on file #1 will exist in file #2 too for sure. Other way round is not true.

Please see attachment for "readable" input I use.

I am working on doing this though SORT in JCL. If there is already any post here similar.. please give me link.

Thank you.
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: Mon Dec 20, 2010 10:32 pm    Post subject:
Reply with quote

Your readable input is not really helpful since it doesn't tell us the starting position, length and format of each field. You need to tell us that. You also need to tell us the RECFM and LRECL of your input file.

Also, please run this job and show the //SYSOUT messages so I can see what level of DFSORT you have:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
//SORTOUT DD DUMMY
//SYSIN DD *
       OPTION COPY
/*
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: Tue Dec 21, 2010 1:14 am    Post subject:
Reply with quote

Hello and welcome to the forum,

When posting data (or code, jcl or anything "fixed" from a screen), use copy/paste and the Code tag.

Because you manually edited your data, it lost alignment. . .

Please do not post attachments as many of our members are not permitted to download attachments.
Back to top
View user's profile Send private message
sathya7586

New User


Joined: 17 Nov 2010
Posts: 4
Location: amsterdam

PostPosted: Tue Dec 21, 2010 2:03 pm    Post subject:
Reply with quote

Thank you all..

I will check and get back to you..
Back to top
View user's profile Send private message
sathya7586

New User


Joined: 17 Nov 2010
Posts: 4
Location: amsterdam

PostPosted: Tue Dec 21, 2010 2:04 pm    Post subject:
Reply with quote

Hi Frank,

I pasted here, the output.

Hi Frank,

Here, i pasted the output you asked me for.

Code:

ICE805I 1 JOBNAME: NEX0444A , STEPNAME: S1
ICE802I 0 BLOCKSET     TECHNIQUE IN CONTROL
ICE201I G RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K51707 C6-K51707 C7-K51707 C8-K51707 E9-K51707 C9-BASE   E5-K51707 E7-K51707
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 09:29 ON TUE DEC 21, 2010 -
                 OPTION COPY
ICE201I G RECORD TYPE IS F - DATA STARTS IN POSITION 1
...
Back to top
View user's profile Send private message
sathya7586

New User


Joined: 17 Nov 2010
Posts: 4
Location: amsterdam

PostPosted: Tue Dec 21, 2010 2:20 pm    Post subject:
Reply with quote

Hi Frank,

As you asked me, Please find details below:

Length of Name: 7
Length of place: 10
Length of Vehicle: 5
Length of Result : 2 (numeric)

#2 file dont have result field.

Total length of work file #1 : 24
Total length of work file #2 : 22.

I am working on our other friends options now..

Thanks for your help..
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 Dec 22, 2010 5:35 am    Post subject:
Reply with quote

Here's a DFSORT job that will do what you asked for:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/24)
//IN2 DD DSN=...  input file1 (FB/22)
//SORTOUT DD DSN=...  output file (FB/24)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,22,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(1,22,A),SORTED
  JOIN UNPAIRED,F2
  REFORMAT FIELDS=(F2:1,22,F1:23,2)
  OPTION COPY
/*
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 getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm


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