Join and Omit records- Master File not to be reformatted.

shajeeth · New User Joined: 25 May 2005 Posts: 20

Hi,

I have two files- Input File 1(FB/LRECL=162)- Master File and Input File 2:(FB/LRECL=24). The master file has a header(two records), data and Trailer(2 records). The format is repetitive which is standard

The header record1 always starts 1111 in first 4 bytes
The header record2 starts with 2222 in first 4 bytes
The trailer record1 starts with 8888 in first 4 bytes-- has the count of detail records + header and sum of amounts in Detail records
The trailer record2 starts with 9999 in first 4 bytes-- has the count of detail records + header + Trailer and sum of amounts in Detail records

I have a pull file(Input File2) which has key 24 bytes(Position 1:24 bytes) which exist in detail records(Position 5:24 bytes) of Input File 1. When i use join to extract/omit the records, as expected the output records are getting reformatted which is not expected.

I tried to split the files based on key and omit and concatenate, but not been able to do so.

There are sums and counts which needs to be re-counted, which i can perform in an additional step

Is there a way to retain the headers and trailers as they exist in input file and omit only records from detail records? Any suggestions/advice?

Input File 1:( FB/LRECL=162)
=============

Nic Clouston · Posted: Wed Jan 27, 2016 6:07 pm

What do you mean by 'reformatted'? Did you really mean re-sequenced?
Show your control cards.

shajeeth · New User Joined: 25 May 2005 Posts: 20

Bill Woodger · Posted: Wed Jan 27, 2016 8:50 pm

It looks like the main file does need to be sorted in the JOINKEYS (which you are doing), but that would mess up the order of the headers/trailers.

If you attach a sequence number to the records, in JNF1CNTL, and SORT on the sequence number in the JOINKEYS main task, that should fix the order.

Ideally you could do the adjustment of totals in the main task as well. It would be rare that you would need a separate step for that.

shajeeth · New User Joined: 25 May 2005 Posts: 20

Bill Woodger · Posted: Wed Jan 27, 2016 11:23 pm

I'm not sure what you were trying to clarify.

It looks like 7777s are a header as well.

1111s, 7777s, 8888s and 9999s don't have a key, unless for "false hits" (so check that that is not possible with the data).

2222s seem to have a key, so will be stripped off if the key matches.

All your output (unmatched F1s, matches) that make up the new file can have a sequence number on, and then you can return them to their original input sequence, even though you've now excluded some records (ie the relative sequence will remain the same).

The number of headers/trailers does not matter.

Remember to chop the sequence number off (OUTREC or OUTFIL) after you've used it.

The trickier part is updating the totals, especially whatever amounts appear on the 8888 and 9999 records because those don't make sense at first glance.