RE: Best Way to find duplicate records in same file

[Joel Fritz <JFritz@xxxxxxxxxxxxxxxx>]

I think this is easier in RPG, but maybe it's just what I'm most comfortable
with. Sort the file by key. If there's no logical, sort it with SQL or
FMTDTA. Then there are two choices.  If you have to do it in place and you
can't replace the file, write a short program that deletes the current
record if it has the same key as the previous record.  If you can replace
the file, rename it, create an empty version, and write a short program that
writes records that don't match the previous key to the new version of the
file.  

It's a chance to speak state machine.  <g>

> -----Original Message-----
> From: Mark Allen [mailto:mallen@xxxxxxxx]
> Sent: Friday, May 09, 2003 11:03 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: Best Way to find duplicate records in same file
> 
> 
> Actually what I need it to do is delete the dups:
> 
> Record 1: ABCDEFGHIJKL
> Record 2: ABCDEFGHIJKL
> Record 3: 123456789012
> 
> I want to delete record 2
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Metz Zak
> Sent: Friday, May 09, 2003 1:50 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Best Way to find duplicate records in same file
> 
> 
> Well here's your SQL method and I thinks it's pretty clean:
> 
> Select KEYFIELD, COUNT(*)
> >From COLLECTION.TABLE
> Group by KEYFIELD
> Having COUNT(*)>1
> 
> KEYFIELD can be replaced with multiple fields and I don't mean key in
> the purest sense, just whatever fields you want to test for 
> uniqueness,
> even all of them. You can also add an ORDER BY to the end of it if
> you're so inclined.
> 
> If you're doing this programmatically, this would become imbedded SQL
> and you would then deal with the results a record at a time.
> 
> Hope that helps!
> 
> > -----Original Message-----
> > From: Mark Allen
> > 
> > I need to add duplicate record checking in a file we 
> received from a 
> > switch.  (I'll just add it to the CL that already FIXES a bunch of 
> > stuff in the file anyways) and was wondering what the best (i.e
> > easy/fast) to
> > do it would be.  I've come up with:
> >  
> > 3.  SQL ???  not done much with it but maybe it could do?
> NOTICE: This E-mail may contain confidential information. If 
> you are not
> the addressee or the intended recipient please do not read this E-mail
> and please immediately delete this e-mail message and any attachments
> from your workstation or network mail system. If you are the addressee
> or the intended recipient and you save or print a copy of this E-mail,
> please place it in an appropriate file, depending on whether
> confidential information is contained in the message.
> 
> 
> _______________________________________________
> 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.cgi/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.cgi/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.
>