Compare 2 files and select matching records from 1 file

kellaurin · Posted: Fri Apr 17, 2009 9:35 pm

I'm confused with using SELECT of SPLICE for this requirement.

I need to extract records from FILE1 where a field from FILE1 matches a field in FILE2 and another field in FILE2 equals a specific value.

IN FILE1 (Variable Blocked), will have dups of field

xxxxxxxxx9001xxxxx....
xxxxxxxxx9002xxxxx....
xxxxxxxxx9002xxxxx....
xxxxxxxxx9003xxxxx....
xxxxxxxxx9001xxxxx....
xxxxxxxxx9003xxxxx....

IN FILE2 - (Fixed Blocked), will not have dups of field

9001P
9002F
9003P

Requirement: Write to output if field matches from FILE1 to FILE2 and FILE2 has a 'P' in position 4.

OUTPUT should look like this, keep original order:

xxxxxxxxx9001xxxxx....
xxxxxxxxx9003xxxxx....
xxxxxxxxx9001xxxxx....
xxxxxxxxx9003xxxxx....

Thanks in advance for helping.

CICS Guy · Posted: Fri Apr 17, 2009 10:23 pm

kellaurin · Posted: Fri Apr 17, 2009 10:39 pm

I'm progressing into the final solution, since I don't understand all of the syntax of the SELECT and SPLICE. I also changed both files to VB since I couldn't get past the length error problem.

I have used SELECT like this, this only gives me one of the matched records.

//TOOLIN DD *

COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(OUT) ON(14,4,CH) FIRSTDUP
/*
//CTL1CNTL DD *
INREC BUILD=(1:1,4,5:C'1',6:5)
/*
//CTL2CNTL DD *
INREC BUILD=(1:1,4,5:C'2',14:5,5)
/*
//CTL3CNTL DD
OUTFIL FNAMES=OUT,INCLUDE=(5,1,CH,EQ,C'1'),
BUILD=(1,4,5:6)
//*

Frank Yaeger · Posted: Fri Apr 17, 2009 10:45 pm

kellaurin,

You need to use SPLICE, not SELECT.

What is the LRECL of file1? What is the LRECL of file2?

Can FILE1 have a key (e.g. 9004) that does not appear in FILE2? If so, you wouldn't want that record in the output file - right?

kellaurin · Posted: Fri Apr 17, 2009 10:50 pm

Frank,

LRECL of FILE1 is VB 4712
LRECL of FILE2 is FB 5

Your statement is correct, FILE 1 will have records with keys that are not in FILE 2 and those records are not wanted in the output file.

Thanks

Frank Yaeger · Posted: Fri Apr 17, 2009 11:07 pm

Here's a DFSORT/ICETOOL job that will do what you asked for:

kellaurin · Posted: Sat Apr 18, 2009 12:07 am

Thanks Frank for taking the time to help. I think I have everything the same but the output file is empty. In fact I saved the temp files and T1 only has VV records not BB records. Here is what I have:

IN1 (Sample Records, not all records) VB 4712
rrrræ99N9104600426501041
rrrræ99N9104600426502041
m ...41N9095395300101071
m ...41N9095395300201071
°.äÉ.19P9104600504701051
°.äÉ.19P9104600505601051
°.äÉ.19P9104600505602051

IN2 (All Records) FB 5
9088P
9092P
9093P
9095P
9097P
9099P
9100P
9102P
9103P
9104F

T1 (not all records) VB 4722
VV00000001rrrræ99N9104600426501041
VV00000002rrrræ99N9104600426502041
VV00000003m ...41N9095395300101071
VV00000004m ...41N9095395300201071
VV00000005°.äÉ.19P9104600504701051
VV00000006°.äÉ.19P9104600505601051
VV00000007°.äÉ.19P9104600505602051

T2(empty)

OUT(empty)

TOOLIN
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)

COPY FROM(IN1) TO(T1) USING(CTL2)

SPLICE FROM(T1) TO(T2) ON(24,4,CH) WITHALL VLENOVLY -
WITH(5,1) WITH(7,4716) USING(CTL3)

SORT FROM(T2) TO(OUT) USING(CTL4)

/*
//CTL1CNTL DD *
INCLUDE COND=(5,1,CH,EQ,C'P')
OUTFIL FNAMES=T1,FTOV,BUILD=(C'BB',20:1,4)
/*
//CTL2CNTL DD *
INREC BUILD=(1,4,5:C'VV',7:SEQNUM,8,ZD,15:5)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=T2,INCLUDE=(5,2,CH,EQ,C'VB')
/*
//CTL4CNTL DD *
SORT FIELDS=(7,8,ZD,A)
OUTREC BUILD=(1,4,5:15)
/*

kellaurin · Posted: Sat Apr 18, 2009 12:47 am

Looking in T1 there are no BB or VB recs. The only records that got written to T1 are records from IN1, not IN2.

Frank Yaeger · Posted: Sat Apr 18, 2009 1:03 am

Kellaurin,

That would happen if you didn't have MOD for T1. Did you miss this in my post?

kellaurin · Posted: Sat Apr 18, 2009 2:18 am

Frank,
I found the problem. My REAL IN1 file only has 8 bytes before the key and I had shown 9 bytes in my original sample. This means that when I ran with the REAL data the key from IN1 was in position 19 and the key from IN2 was in position 20, which is why you had ON(24,4,CH).

I have modified it to align the keys in T1 and it now works perfectly.

Thanks again for your help. I am trying to convince others in our shop to use SORT or ICETOOL instead of coding COBOL programs, but I need to be able to solve some problems like this one by myself.

Thanks,
Kelly

Frank Yaeger · Posted: Sat Apr 18, 2009 3:36 am

Ok, good. I'm glad you figured it out.