-2

I'm doing a program that reads all records in physical file using logical file(transaction code, transaction date and transaction amount), checks whether there are 2 or more records with same trans code and trans date, and determine what's the largest amount. I'm supposed to retain the record with the largest amount and delete(delete flag='D') others (with same trans code and trans date).

How will I know what's the record with the largest amount? I mean, if there are 1000 records and I found 7 identical trans code and trans date, if the largest amount is record 7th, how do I delete records 1 to 6 when after reading the file, file pointer goes to the next record,right?

I do apologize if this comes as confusing. This is confusing me as well.

mikecj
  • 9
  • 1

1 Answers1

0

I would use embedded SQL for something like this. Without knowing the format of your table, you could use aggregation to find the records to update, something like this:

exec sql
  update table a
    set deletecode = 'D'
    where amount <> (select max(amount) from table
                     where trancd = a.trancd);

This works by finding the max(amount) for rows with the same trancd as the one being updated. If the amount on the row being updated is different from the max(amount) for that trancd, then go ahead and update the deletecode, otherwise do not update it. There is no need to check whether there are two or more records since if there is only one record, the max(amount) will be the same as the amount field on the transaction to be updated.

jmarkmurphy
  • 11,030
  • 31
  • 59