RE: Duplicate records

should be a delete statement:

with tempFile as                                                    
  (select 
        keyField, max(rrn(ACTUALFILE)) as uniRRN 
   from ACTUALFILE
   group by keyField
   order by KeyField)
  delete from ACTUALFILE where not exist(select * from tempFile where 
rrn(ACTUALFILE)=uniRRN)

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Lim Hock-Chai
Sent: Thursday, December 30, 2004 2:13 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Duplicate records


haven't tested it yet.  I think you might be able to do this statement on 
interactive sql:

with tempFile as                                                    
  (select 
        keyField, max(rrn(ACTUALFILE)) as uniRRN 
   from ACTUALFILE
   group by keyField
   order by KeyField)
  select * from ACTUALFILE exception join tempFile on rrn(ACTUALFILE)=uniRRN

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Rosh De
Sent: Thursday, December 30, 2004 1:19 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Duplicate records



I have a PF with no keys. All records in this PF are duplicate, exactly same. 
What is a better way to remove all such duplicates? This is one time problem.

I thought of a program to read the file, read first record, keep it, read 
second and delete it and reset the counter. SQL of any help here?

-Rosh


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.