Select records from 2'nd file that doesn't have matching key

Ramanan-R · Posted: Wed Oct 10, 2007 5:04 pm

Hi All,

I have two files of LRECL=80, with key as first 8 bytes. I need to select records of first file which doesn't have a matching key in second file.

nuthan · Posted: Wed Oct 10, 2007 5:09 pm

U can search this in forum, it was posted here previously many times .

Aaru · Posted: Wed Oct 10, 2007 5:26 pm

This can be done using the SELECT operator of ICETOOL. As suggested, search this forum.

Aaru · Posted: Wed Oct 10, 2007 5:43 pm

ramanan,

What should happen if there are unique values in the file2? Should we extract that or not?

Ramanan-R · Posted: Wed Oct 10, 2007 6:25 pm

Aaru,

Thanks for your reply, but i need unique records only from file 1 and not from File2.

Suppose, if file 1 has the below records,

11111111 XXX
22222222 YYY
33333333 ZZZ

and file 2 with below records,

11111111 AAA
22222222 BBB
44444444 CCC

For the above, the output should only contain unique record from file 1 as below,

33333333 ZZZ UNDEFINED

and not as given below

33333333 ZZZ UNDEFINED
44444444 CCC UNDEFINED

Thank you,

Regards,
Ramanan R

Frank Yaeger · Posted: Wed Oct 10, 2007 9:58 pm

Ramanan R,

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

Ramanan-R · Posted: Thu Oct 11, 2007 11:23 am

Thanks Frank,

That was working fine, Can we build two records for every unique record of file 1. Like below,

33333333 ZZZ UNDEFINED HEADER RECORD
33333333 ZZZ UNDEFINED DETAIL RECORD

Regards,
Ramanan R

Ramanan-R · Posted: Thu Oct 11, 2007 2:56 pm

Thanks all,

I built two records for every unique record of file 1 using '/' in BUILD.
Like below,

Aaru · Posted: Thu Oct 11, 2007 3:28 pm

You are welcome. Thanks for letting us know the final solution.