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
 

 

How to select records from multiple files

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

New User


Joined: 09 Apr 2015
Posts: 4
Location: India

PostPosted: Fri Apr 10, 2015 2:51 pm    Post subject: How to select records from multiple files
Reply with quote

Im a tester using File-Aid to select some records from a file. Now my requirement is to select the records based on field1 from File1 and another field2 in File2. Account number field is common in both the files.

Is there any way to filter my data like above?
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Fri Apr 10, 2015 3:31 pm    Post subject: Reply to: How to select records from multiple files
Reply with quote

Does the File-Aid documentation give you any clues or pointers? Your colleagues?

Easy task for SORT with JOINKEYS.
Back to top
View user's profile Send private message
Balaji C

New User


Joined: 09 Apr 2015
Posts: 4
Location: India

PostPosted: Fri Apr 10, 2015 3:46 pm    Post subject:
Reply with quote

Thanks Woodger!

My colleagues dont have knowledge on mainframe. I dont have much expertise on join keys. Could you please explain me how to select some accounts based on Feild1=51 from File1 and Field2=1 thru 4 from File2. DDn1 and DDn2 are the DD names respectively

Control statements will be more helpful icon_smile.gif
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Fri Apr 10, 2015 6:09 pm    Post subject:
Reply with quote

Hello Balaji,
I understand that position of Field1 start at 51.

Your control statements could be like this:

//JOINK2 EXEC PGM=SORT,COND=(0,NE)
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=DDN1,
// DISP=SHR
//IN2 DD DSN=DDN2,
// DISP=SHR
//SORTOUT DD DSN=DDOUT,
// SPACE=(CYL,(40,20),RLSE),
// DISP=(,CATLG,DELETE)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(51,4,A)
JOINKEYS F2=IN2,FIELDS=(01,4,A)
REFORMAT FIELDS=(F1:1,LENGTHDDN1,F2:1,LENGTHDDN2)

OPTION COPY
/*

In the REFORMAT FIELDS command you can build the output file format. In the example tha output file contains both records.


Good luck
Regards
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Apr 10, 2015 7:02 pm    Post subject: Reply to: How to select records from multiple files
Reply with quote

Balaji,

It's very disappointing that you didn't even try to Google and search for JOINKEYS when Bill suggested and you directly asked for the Control statements itself.

It wouldn't have taken more than 15 mins to do all what you wanted.

From the next time, please Google, Try, Fail and then ask others for help.

.
Back to top
View user's profile Send private message
Balaji C

New User


Joined: 09 Apr 2015
Posts: 4
Location: India

PostPosted: Mon Apr 13, 2015 2:43 pm    Post subject:
Reply with quote

rinsio wrote:
Hello Balaji,
I understand that position of Field1 start at 51.

Your control statements could be like this:

//JOINK2 EXEC PGM=SORT,COND=(0,NE)
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=DDN1,
// DISP=SHR
//IN2 DD DSN=DDN2,
// DISP=SHR
//SORTOUT DD DSN=DDOUT,
// SPACE=(CYL,(40,20),RLSE),
// DISP=(,CATLG,DELETE)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(51,4,A)
JOINKEYS F2=IN2,FIELDS=(01,4,A)
REFORMAT FIELDS=(F1:1,LENGTHDDN1,F2:1,LENGTHDDN2)

OPTION COPY
/*

In the REFORMAT FIELDS command you can build the output file format. In the example tha output file contains both records.


Good luck
Regards

Thank You very much Rinsio. I have applied your logic with some modicications from Google. Finally made what I required. Thanks Again.]
Back to top
View user's profile Send private message
Balaji C

New User


Joined: 09 Apr 2015
Posts: 4
Location: India

PostPosted: Mon Apr 13, 2015 2:45 pm    Post subject: Re: Reply to: How to select records from multiple files
Reply with quote

RahulG31 wrote:
Balaji,

It's very disappointing that you didn't even try to Google and search for JOINKEYS when Bill suggested and you directly asked for the Control statements itself.

It wouldn't have taken more than 15 mins to do all what you wanted.

From the next time, please Google, Try, Fail and then ask others for help.

.

Rahul, Thanks for your suggestion. I have applied above logic with some modifications from Google. Finally able to make it.
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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