Deleting Duplicate Record from PF

1) SQL Statement.

Displays duplicate record from file.

Select * From LIB/MEMBER A

Where RRN(A) >

(Select Min(RRN(B)) From LIB/MEMBER B

Where A.LastName = B.LastName)

Delete’s duplicate record from file

DELETE FROM BHABANI/TESTPF1 A WHERE RRN(A) > (Select Min(RRN(B))

From bhabani/testpf1 b where a.FLD1=b.fld1 )                   

-------------------------------------------------------------------------------------------------

2) Series of Operation

• We can use the simple CPYF (Copy File) command.
• Create the same file under a different name and define the whole field as key. Also define the key as UNIQUE.
• CPYF the original file to the new file with ERRLVL(*Nomax).This will copy only the FIRST occurence of duplicate records.
• After the CPYF finished, you could copy the new file's contents back to the original file with MBROPT(*Replace).
• After that, the original file WILL NOT contain any duplicate records.