Posted: Wed Apr 23, 2003 3:53 pm Post subject: Selecting records from File 2 based on records from File 1
Hi,
I have 2 files with me, say File A and File B. The data in these 2 files is:
File A
Name
Emp Number
DoB
File B
Name
Dept
Gender
Now file B has many many more records than File A. Thus I want to select the records from file B which match the records in file A.
How can I do this thru JCL??
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Wed Apr 23, 2003 7:03 pm Post subject:
Puru,
Are Name, Emp Number and DoB the fields in each record, or are they each in a separate record? Likewise, for Name, Dept and Gender?
Please show us some sample input records and what you want the output to look like. Also, what is the RECFM and LRECL of the input files? _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Frank,
Guess I might be more clear thru this... Both the files are FB with different LRECLs. I have listed the files below
*************************************
File A
*************************************
Record 1 :: Name1 Dob1 Dept1 Emp#1
Record 2 :: Name2 Dob2 Dept2 Emp#2
..
..
..
..
..
..
*************************************
File B
*************************************
Record 1 :: Name1 Emp#1 Grade
Record 2 :: Name2 Emp#2 Grade
..
...
..
..
..
..
************************************
Now say the grade in file B can be either A or B or C. Now say I sort the file B for all records with grade A's
Now based on the emp# in the sorted file ( of file B), I have to pull out the corresponding records from File A.
Name1 in file B is not the same as Name1 in file A.
I can do this through a program, but wanted to know if this is possible thru a JCl.
Name1 Emp#1 A
Name2 Emp#2 B
Name3 Emp#3 A
Name4 Emp#4 C
Name5 Emp#5 A
Neither file has duplicate EMP#s.
Here's a DFSORT/ICETOOL job that will do what I think you want:
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILEB DD DSN=... Input File B
//TB DD DSN=&&TB,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=... Input File A
// DD DSN=*.TB,VOL=REF=*.TB,DISP=(OLD,PASS)
//OUT DD DSN=... Output File
//TOOLIN DD *
* Keep only records with grade of 'A' from File B and
* reformat those records so the employee number is in the
* same positions as in File A
COPY FROM(FILEB) TO(TB) USING(CTL1)
* Keep only the records with a match on employee number
* in File A and the reformatted File B
SELECT FROM(CON) TO(OUT) ON(18,5,CH) FIRSTDUP
/*
//CTL1CNTL DD *
INCLUDE COND=(13,1,CH,EQ,C'A')
OUTREC FIELDS=(18:7,5,80:X)
/*
If this doesn't do what you want, then you'll need to give me more specific information about what you're trying to do, including samples of the input for File A and File B, and the output you want. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Frank,
Sorry for the delay, was out of circulation.Thanks a ton, this worked great!!!. However I have another doubt in this, what if the the second file has duplicate entries and I still want to pull out the entries matching those in file 1. Please let me know.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Wed May 21, 2003 1:04 pm Post subject:
Puru,
If the second file had duplicate entries, you could do a SELECT with FIRST against it to remove the duplicates and use the output of that SELECT as the second file in the concatenation for the second SELECT.
For more complicated situations with duplicates, you could use the new SPLICE opreator available with DFSORT R14 PTF UQ90053 (Feb, 2003). It can do lots of different kinds of join and match operations. For details and examples, see:
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
