Two file matching:

Max 1 record per id on file 1 and multiple records allowed on file 2.

A record on file 2 without a matching record on file 1 is an error.

Speaker Notes

Slide 1:

More information on processing 2 files sequentially. In this example, file 1 has 1 record per id and file 2 has multiple records per id some of which will have multiple records per id.

Slide 2:

Prior to processing this logic, the first record is read from each file. Processing is continued until EOF is reached on both files.

The logic being shown in this example is:

Slide 3:

The first record from file1 and file 2 both have id of 111. The amt from the record on file2 is added to the total accumulator (TOT-ACCUM). Then a new record is read from file 2.

Slide 4:

The second record with the id of 111 in the file2 has its amount added to the accumulator and then another record is read from file2.

Slide 5:

The record is written when all records with id of 111 from both files have been processed and the total from these records has been accumulated. The total includes the 01 and the 10 from file 2 and the 100 from file1. The total of 01 + 10 + 100 = 111.

Note that after the record is written and a new record is read from file 1, the accumulator is set to 0.

Slide 6:

The ids on the two files match, so I process by adding the amt to the total accumulator and then I read a record from file 2. Remember the accumulator was set to zero after the record was written.

Slide 7:

I add 222 to the 22 that is already in the accumulator to get 244.

I then read another record from file 2. The record I read has an id of 300 so the next comparison will be between 222 on file 1 and 300 on file 2.

Slide 8:

Since ID1 (222) is less that ID2 (300) I know that all of the records with id of 222 have been processed from file 2. Therefore, it is time to add the amount from file 1 to the accumulator giving me a total of 444 and then write the record to the output file. I then read another record from file 1 and set the accumulator to 0. The next comparison will be between ID1 (333) and ID2 (300).

Slide 9:

In this processing, we said that there had to be a record on file 1. Since there is no ID1 (300) then the ID2 (300) on file 2 is invalid and is handled as an error. At that point another record will be read from file 2.

Slide 10:

This is an example of a record from file 1 with no matching records on file 2. This could be an example of a master file (file 1) with no activity (file 2).

Slide 11:

When file 1 and file 2 match it can be seen as having activity in file 2 that applies to the single record in file 1. I need to process all of the matching activity records in file 2 so I will continue reading file 2 until there is a change in id.

Slide 12:

This is the second record from file 2 that matches to the id of 444 in file 1. The amount is added to the accumulator and the next record is read. The next comparison will also be between matching records with id 444.

Slide 13:

Again I have a match so I am processing the third record with id of 444. The next record read from file 2 will be 555 so the next comparison is between 444 on file 1 and 555 on file 2. This will signal that we have finished all of the activity from file 2 for 444 so the record on file 1 will be dealt with and then the record will be written to the disk.

Slide 14:

The record for 444 is now written and the next processing will be for 555 in both files.

Slide 15:

This is another equal to and as the flowchart segment says we need to add the contents of amt to the accumulator making it 006 and then read another record from file2 since the record on file2 was the one that got processed.

Slide 16:

I now have the id on file 1 less than the id on file 2 so I know that I have processed all of the records on file 2 with that id. That means that it is time to complete the processing of that id by adding the amount from file 1 to the accumulator and writing the record on the output file. Then I read a new record from file 1 to give myself the base to compare against and 0 out the accumulator.

Note that because there is only 1 record per id from file 1 and that I cannot have a record from file 2 that does not match the record in file 1, the id in file 1 because the base which I compare against.

Slide 17:

Again, I have equal records on the two files so I am simply adding to the accumulator and reading another record from the file that can have multiple records per id.

Slide 18:

This is processing where there is a match between the two files. Therefore we are simply accumulating the data from file 2 and then since file 2 has been processed, reading another record from file 2.

In sequential processing, you always read from the file where you have just processed a record.

Slide 19:

Since 666 was less than 777, I know that I have processed all of the records from file 2 with an id of 666. That means it is time to add the amount on the 666 record from file 1 to the accumulator and write the record. Now I have processed the record on file 1 so it is time to read a new record from file 1. I am starting a new group so it is also time to zero out the accumulator.

Slide 20:

You can handle eof by moving 999 or high values to the id so file 1 will always compare low or you can code special processing to handle the situation where you reach eof on file 1 first and special processing to handle the situation where you reach eof on file 2 first etc.

Slide 21:

EOF has now been reached on both files and since the loop was performed until eof on both files was reached, processing will stop.