Re: Store Record Format in an Array

[Raul A Jager <raul@xxxxxxxxxx>]

   L1 is an exelent option.  I didn't want to create he logical file, so I
   have used SQL.  First, find out how many records are dupicated:

       > SELECT NCLIE, TESTAD, TELEF, count(*) FROM b.telefonos GROUP BY
   NCLIE, TESTAD, TELEF HAVING count(*) > 1

   Deleting from the same file should work, but it is a lot faster to use a
   "work file":

       > insert into duplicados SELECT dupli from
         (SELECT NCLIE, TESTAD, TELEF, count(*), max(rrn(t)) dupli
         FROM b.telefonos t GROUP BY NCLIE, TESTAD, TELEF HAVING count(*) >
   1) d

         5 fila(s) insertada(s) en DUPLICADOS.

       > delete from b.telefonos x where rrn(x) in (SELECT dupli from
   duplicados )

   This procedure deletes one of the copies, in the first step I found that
   some records where two times and others tree. I had to repeat the select
   and delete for the records that where tree times.

   If you have to many duplicates, it may be more efficient to create one
   record for each key, and delete the rest.
   
_________________________________________________________________________________

   Buck wrote:

 I would like my program to delete duplicate records in a file.
    

 This is what I would do

 1) FMTDTA/OPNQRYF/logical file to put the records in order
     by the whole record.
 2) Program describe the file and put L1 in the Level break column.
 3) NL1, delete the record.

 An alternative is to read every record and CHAIN to itself to see if it's a
 duplicate.  You can use a logical with a different name from the physical
 but with the same key.

 Something to get the discussion started.
   --buck



 _______________________________________________
 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.