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
 

 

Combining values from records in 2 files

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

New User


Joined: 14 Apr 2009
Posts: 14
Location: India

PostPosted: Wed Sep 22, 2010 10:40 pm    Post subject: Have a similar request
Reply with quote

Hi,

I have two files. One (File1) having around 1k records. Another (File2) having only 5 records.

File1 structure:
0001EBCD123456
0001MBCD123457
0001DBCD123458
0001BBCD123459
0001MBCD123450
0001FBCD123451
0001EBCD123452
0001MBCD123453
0001BBCD123453
0001DBCD123453

File2 structure:
E B
M B
D N
B N
F B

I need to compare the bold values from File1 with file2 and if match, append the rest of the record from file2 to file1.

O/P file will look like this -

0001EBCD123456 B
0001MBCD123457 B
0001DBCD123458 N
0001BBCD123459 N
0001MBCD123450 B
0001FBCD123451 B
0001EBCD123452 B
0001MBCD123453 B
0001BBCD123453 N
0001DBCD123453 N

Could someone please help with this?
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: Wed Sep 22, 2010 10:53 pm    Post subject:
Reply with quote

Hello,

When you have a question, you should start a new topic for your question rather than posting a reply to some existing unrelated topic.
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 Sep 22, 2010 10:55 pm    Post subject:
Reply with quote

Give the RECFM and LRECL of each input file.

Give the starting position, length and format of each relevant field.

Your example does NOT show any non-matching records. Can you have a record in file1 without a match in file2? Can you have a record in file2 without a match in file1? If so, show a better example of input and output with these variations.
Back to top
View user's profile Send private message
NandanPhalak

New User


Joined: 14 Apr 2009
Posts: 14
Location: India

PostPosted: Wed Sep 22, 2010 11:12 pm    Post subject:
Reply with quote

Hi Frank,

Sorry for not providing sufficient data.
File1 LRECL - 14 bytes, RECFM - FB
File2 LRECL - 3 bytes, RECFM - FB

File1 field starts at position 5, length 1 byte. This field need to be compared with position 1 of File2. If this matches, append the last 2 bytes (or remaining bytes) of file2 to file 1.

I can have a record in file2 without a match in file1 but not vice-versa.

Thanks for your time and support!
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 Sep 22, 2010 11:25 pm    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/14)
//IN2 DD DSN=...  input file2 (FB/3)
//SORTOUT DD DSN=...  output file (FB/16)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(5,1,A)
  JOINKEYS F2=IN2,FIELDS=(1,1,A)
  REFORMAT FIELDS=(F1:1,14,F2:2,2,F1:15,8)
  SORT FIELDS=(17,8,ZD,A)
  OUTREC BUILD=(1,16)
/*
//JNF1CNTL DD *
  INREC OVERLAY=(15:SEQNUM,8,ZD)
/*
Back to top
View user's profile Send private message
NandanPhalak

New User


Joined: 14 Apr 2009
Posts: 14
Location: India

PostPosted: Thu Sep 23, 2010 12:07 am    Post subject:
Reply with quote

Thanks much Frank!

Couple of questions:
1. I am getting a syntax error on this code saying "BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY".. how to resolve this?
I tried giving space in 1st position and also in 1st and 2nd position.

2. Could you please explain how this works, espcially the CNTL part? How the CNTL part is getting used in the SORT, I am not able to see any connection? Am I missing here something?
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: Thu Sep 23, 2010 1:03 am    Post subject:
Reply with quote

1. That probably means you don't have the Nov, 2009 PTF which supports JOINKEYS installed. Ask your System Programmer to install it (it's free).

2. JNF1CNTL supplies control statements for the F1=IN1 task. It adds a sequence number at the end of the file1 input records so we can sort them back into their original order after the join.

For complete details on the PTF and JOINKEYS, see:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
Back to top
View user's profile Send private message
NandanPhalak

New User


Joined: 14 Apr 2009
Posts: 14
Location: India

PostPosted: Thu Sep 23, 2010 5:42 am    Post subject: Reply to: Combining values from records in 2 files
Reply with quote

Thank you so much!!

Appreciate your help!
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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm


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