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
 

 

search for matching Value then substitue in F1

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

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Tue Nov 18, 2008 2:23 pm    Post subject: search for matching Value then substitue in F1
Reply with quote

Hi,
I have 2 files. The File 1 (F1) has customer details with the CUST-ID being the key in this file. File 2 (F2) has the CUST-ID and the corresponding ACCT-NBR for each customer id beside it apart from other details.
I need to match and then write the ACCT-NBR from F2 to come into the place of the CUST-ID in F1. Thereby replacing the CUST-ID in F1 with the matching ACCT-NBR from F2.
I need to do this because there is a third file(F3) which has ACCT-NBR as the main key along with almost 550 bytes of other information. If I can get a common key field in F1 and F3, I can follow it up with some more processing I need with SORt using JOIN KEYS. The problem is ; as I stated above - there isn't a common Key between F1 and F3.
Eg:
F1==>
Code:

CUST-ID00122....
CUST-ID00123....
CUST-ID00124....
CUST-ID00125....

F2===>
Code:

CUST-ID00122 ACCT-NBR010
CUST-ID00123 ACCT-NBR020
CUST-ID00124 ACCT-NBR030
CUST-ID00125 ACCT-NBR040

OUTPUT===>
[/code]
ACCT-NBR010...
ACCT-NBR020...
ACCT-NBR030...
ACCT-NBR040...
Code:
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Tue Nov 18, 2008 6:12 pm    Post subject:
Reply with quote

JayC,

Your example does not seem to explain clearly what you're trying to do. Please post some "real" data from all the 3 files with all possible combinations including CUST-ID from file-1 not found in file-2 and vice versa.
Back to top
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Wed Nov 26, 2008 2:34 pm    Post subject:
Reply with quote

Arun hope the following will be enough :
F1==>
Code:

CUST-ID00122SANDERSPETERXXXXX0481424418
CUST-ID00121MARYJONESONEXXXXX0481422318
CUST-ID00124SANDYUMESHERXXXXX0481424458
CUST-ID00123JOANDICHERERXXXXX0481424568
CUST-ID00124ROSHANNPETERXXXXX0481423418
CUST-ID00125JOHNKOLENCHIXXXXX0481424938
CUST-ID00122SANDEEPKRISHXXXXX0481424964
CUST-ID00122JOETHOMASCHEXXXXX0481424968
CUST-ID00125PAVANUPPUMANXXXXX0481424966


F2==>
Code:


CUST-ID00121ACCT-NBR0101XXXXX
CUST-ID00122ACCT-NBR0102XXXXX
CUST-ID00123ACCT-NBR0103XXXXX
CUST-ID00124ACCT-NBR0104XXXXX
CUST-ID00125ACCT-NBR0105XXXXX


FINAL OUTPUT ==>
Code:


ACCT-NBR0102SANDERSPETERXXXXX0481424418
ACCT-NBR0101MARYJONESONEXXXXX0481422318
ACCT-NBR0104SANDYUMESHERXXXXX0481424458
ACCT-NBR0103JOANDICHERERXXXXX0481424568
ACCT-NBR0104ROSHANNPETERXXXXX0481423418
ACCT-NBR0105JOHNKOLENCHIXXXXX0481424938
ACCT-NBR0102SANDEEPKRISHXXXXX0481424964
ACCT-NBR0102JOETHOMASCHEXXXXX0481424968
ACCT-NBR0105PAVANUPPUMANXXXXX0481424966


Length of F1 = 39
Length of F2 = 29

The Customer-ID and Acct-Nbr fields are equal namely 12bytes. Only the matchin Acct-nbr from F2 should be substituted in place of the Customer -ID.

Sorry for the late reply
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Wed Nov 26, 2008 7:33 pm    Post subject:
Reply with quote

JayC,

As asked before, what if the CUST-ID from file1 does not have a matching entry in file2? For e.g., Consider a record in your file1 like this.
Code:
CUST-ID00999SANDERSPETERXXXXX0481424418
What should be the expected output for this record?
Back to top
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Wed Dec 03, 2008 10:37 am    Post subject:
Reply with quote

Arun,

All the CUST-ID will have a matching Entry in File 2. This is because the CUST-ID's in File 1 are used to get the ACCT-NBRs from the DB. Only problem is that - the ACCT-NBR are in a another file namely (F2).
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Wed Dec 03, 2008 11:21 am    Post subject:
Reply with quote

JayC,

The below Syncsort job does what you want to achieve. The output records will be sorted in the order of CUST-ID in file-1. If you want to preserve the input order, you can modify this to add a sequence field before join and a sort after join.
Code:
//STEP1    EXEC PGM=SORT               
//SORTJNF1 DD DSN=file1 (FB, LRECL=39)
//SORTJNF2 DD DSN=file2 (FB, LRECL=29)     
//SYSOUT   DD SYSOUT=*                 
//SORTOUT  DD SYSOUT=*                 
//SYSIN    DD *                       
  JOINKEYS FILE=F1,FIELDS=(1,12,A)     
  JOINKEYS FILE=F2,FIELDS=(1,12,A)     
  REFORMAT FIELDS=(F2:13,12,F1:13,27)
  SORT FIELDS=COPY
SORTOUT
Code:
ACCT-NBR0101MARYJONESONEXXXXX0481422318
ACCT-NBR0102SANDERSPETERXXXXX0481424418
ACCT-NBR0102SANDEEPKRISHXXXXX0481424964
ACCT-NBR0102JOETHOMASCHEXXXXX0481424968
ACCT-NBR0103JOANDICHERERXXXXX0481424568
ACCT-NBR0104SANDYUMESHERXXXXX0481424458
ACCT-NBR0104ROSHANNPETERXXXXX0481423418
ACCT-NBR0105JOHNKOLENCHIXXXXX0481424938
ACCT-NBR0105PAVANUPPUMANXXXXX0481424966
Back to top
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Wed Dec 03, 2008 7:10 pm    Post subject:
Reply with quote

Thanks Arun
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 Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Matching and unmatching records scdinesh DFSORT/ICETOOL 11 Tue Feb 07, 2017 12:41 am
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am
No new posts TSO Logon Failing - Acount Not matching SLNewton TSO/ISPF 1 Thu Aug 25, 2016 9:37 pm
No new posts Search a Load Module sreekanth1984 TSO/ISPF 8 Tue Jun 07, 2016 8:45 pm


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