Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Merge/update fields when keys match, otherwise leave alone

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

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Wed Jan 18, 2012 12:12 am    Post subject: Merge/update fields when keys match, otherwise leave alone
Reply with quote

Apologies if this has been covered but I searched and could not find this particular flavour of a merge request:

I have two files:
- a large keyed master file
- a second smaller keyed file with values for a field I want to drop into the first file where keys match, if no match is found then I want values in master file left alone.

To say it more in English: I have a keyed file of a thousand records where one of the many fields on each record is birthday. On the second file I have 100 records with more accurate birthday values. I want to drop these 100 updated birthdays into the appropriate records (matching key) on the master. If there is no match on key then I want the data currently there not be overwritten but left alone. So 900 records would be unchanged but the 100 would have updated birthdays.


So if master file looks like this
Code:

KEY1 MALE   20010101
KEY2 FEMALE 20010202
KEY3 MALE   20010303


and other file looked like this:

Code:

KEY2 FEMALE 20019999


then I would want output to look like this:

Code:

KEY1 MALE   20010101
KEY2 FEMALE 20019999
KEY3 MALE   20010303


So master records with no match on the second file are left alone but when there is a match the birthday is overwritten with new value. Those Male and Female entries on the records are not part of the key, they are just examples of other fields on the record being left alone.
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 Jan 18, 2012 12:56 am    Post subject:
Reply with quote

You can use DFSORT's JOINKEYS function to do this kind of thing. For complete details on DFSORT's JOINKEYS, see:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/4.0?DT=20110608113434

If you need more specific help, tell me the RECFM and LRECL of each input file, if the records in each file are already sorted by the key or not, if either or both files can have duplicates within it, and the starting position, length and format of all relevant fields in the input files.
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Wed Jan 18, 2012 1:10 am    Post subject:
Reply with quote

Thanks for your help! I might be able to figure this out myself but am under a bit of a time crunch so I'll pass you on the info:

Each file is RECFM=FB and LRECL=148. Both are already sorted on the key which is the first 20 bytes. No duplicate keys are permitted or exist in either file.

If the keys match (columns 1 thru 20), then I want columns 142 thru 148 from the second file to be placed in the same columns in the first file(overwriting what is there). All other columns to be left as is. And if no match is found on the second file then I want the original record left alone. All columns are char.

I believe/hope that that is everything you need.
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 Jan 18, 2012 1:54 am    Post subject:
Reply with quote

You can use a DFSORT JOINKEYS job like the following. You say you want columns 142-148, but since your date is actually 8 bytes long, I'm guessing you really want 141-148. If not, adjust accordingly.

Code:

//S1 EXEC PGM=SORT                                   
//SYSOUT DD SYSOUT=*                                 
//IN1 DD DSN=...  input file1 (FB/148)                   
//IN2 DD DSN=...  input file2 (FB/148)                   
//SORTOUT DD DSN=... output file (FB/148)               
//SYSIN DD *                                         
  JOINKEYS F1=IN1,FIELDS=(1,20,A),SORTED             
  JOINKEYS F2=IN2,FIELDS=(1,20,A),SORTED             
  JOIN UNPAIRED,F1                                   
  REFORMAT FIELDS=(F1:1,148,?,F2:141,8)             
  OPTION COPY                                       
  INREC IFOUTLEN=148,                               
    IFTHEN=(WHEN=(149,1,CH,EQ,C'B'),                 
      OVERLAY=(141:150,8))                           
/*
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Wed Jan 18, 2012 10:49 pm    Post subject:
Reply with quote

Frank - Thanks for sorting this out for me! I'm so glad I found this board!
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 Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am
No new posts Split file based on comparsion to 2 f... Div Grad DFSORT/ICETOOL 3 Thu Jul 27, 2017 9:16 pm
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us