Re: Removing duplicate records

I was really lucky. The file is just a container for incomplete
records that need a field coded. An extremely simple RPG program
reads the file, and if the lone field has been updated in the main
files, the record gets written to a transmittal file. No possibility
of record locks. The more I think about it, even if I had not removed
the duplicates, the vendor would have received two idential update
records, so there really wasn't an issue - except for the sloppiness.

Thanks so much for all the ideas.

John McKee


On Fri, Jan 20, 2012 at 11:16 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

John,

In your case, as your duplicate records are identical, using DISTINCT for
each field is a simple (and valid!) solution.
But, what happens is even one of your fields is different, although the
Account number is the same (think of, for example, a LASTUPDATED field)?
Another problem would be if you can't allocate your table (is always in
use).

Also, I believe you could simplify Gary's idea even further by using:

CREATE TABLE  mylib/wkfile  AS
    ( SELECT  DISTINCT *
        FROM  alib/dupfile )
    WITH DATA

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Fri, Jan 20, 2012 at 12:09 PM, John Yeung <gallium.arsenide@xxxxxxxxx>wrote:

On Fri, Jan 20, 2012 at 10:54 AM, Gary Thompson <gthompson@xxxxxxxxxxx>
wrote:

John
Create an identical work file
Then use SELECT DISTINCT to select from table with dups,
       inserting into your work file:

CREATE TABLE  mylib/wkfile  AS
     ( SELECT  DISTINCT fld1, fld2, ... fldn
         FROM  alib/dupfile )
     WITH DATA

Of course!  DISTINCT is perfect for this.  I feel quite silly for not
remembering this keyword.  (I totally do not use SQL enough.)

The other solutions probably have their selling points, but I find
this one the most elegant and easiest to understand.

John Y.  (not to be confused with John McKee)
 --
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.