Duplicate keys

[Guest.Visitor]

I have a file with duplicate keys. I would like to eliminate all but one of the occurances of these duplicates. Is there a way to do this? thanks.

[Guest.Visitor]

R.O., I haven't tested this, but I believe it will work.

Delete from lib/filnm f where rrn(f) in (select min(rrn(g)) from lib/filnm g where f.keyflda=g.keyflda and f.keyfldb=g.keyfldb having count(*)>1)

This would be MUCH easier and faster in RPG. Bill R.O. Weber wrote: I have a file with duplicate keys. I would like to eliminate all but one of the occurances of these duplicates. Is there a way to do this? thanks.

[Guest.Visitor]

I usually use OPNQRYF to do this. There is a parameter called UNIQUEKEY that will select only the first record for unique each key value when the parm is set to *All. I use CPYFRMQRYF to create a temporary file, then a CPYF to replace the original with the temporary. Here is a CL example:

 PGM OVRDBF FILE(MASTER) SHARE(*YES) /*-- USE OPNQRYF TO SELECT ONLY UNIQUE KEYED RECORDS --*/ OPNQRYF FILE((MASTER)) KEYFLD(*FILE) UNIQUEKEY(*ALL) /*-- COPY UNIQUE KEYED RECORDS TO TEMPORARY FILE --*/ CPYFRMQRYF FROMOPNID(MASTER) TOFILE(QTEMP/MASTERTEMP) + CRTFILE(*YES) CLOF OPNID(MASTER) /*-- COPY TEMPORARY FILE BACK TO MASTER FILE, REPLACING RECORDS. --*/ CPYF FROMFILE(QTEMP/MASTERTEMP) TOFILE(MASTER) + MBROPT(*REPLACE) ENDPGM

Mark McCall

---

On Saturday, December 05, 1998, 07:41 AM, R.O. Weber wrote: I have a file with duplicate keys. I would like to eliminate all but one of the occurances of these duplicates. Is there a way to do this? thanks.

[Guest.Visitor]

On Saturday, December 05, 1998, 07:41 AM, R.O. Weber wrote: I have a file with duplicate keys. I would like to eliminate all but one of the occurances of these duplicates. Is there a way to do this? thanks. Many ways... but depending on your desire on how to have the duplicates resolved/handled, the request could be different. Do you desire to maintain as unique the first by relative record number, the first one entered into the DB, etc.? If there is a large amount of records but few duplicates you would do best to delete those which are selected as duplicates; position to and delete all those following with the nextequal. If there are mostly duplicates with fewer records, then insert into a unique keyed file of a selection of uniques from the original file is very quick; and with many records insert into non-keyed, and add the unique key later -- I am assuming you want rules to prevent the condition in the future. Regards, Chuck Comments provided "as is" with no warranties of any kind whatsoever.