Our client needs strong evidence of problems in a system that needs
revamping. They want a list of present problems, and duplicate records are
just one. Regarding duplicates, they have a choice between knowing which
records have duplicates and how many, and listing all duplicates. Senior
management wants all duplicates listed. I suspect they are looking for a
cannon. What we plan to do is to give them both, as a pleasant surprise.
By the way, it is a mainframe Adabas system from Software AG. For reasons
of efficiency, I am using Rbase to look for problems in the key data
downloaded from the mainframe.
Ben, hope your curiosity is met. Also thank you for your idea. BTW, did
you test
it out with real data?
Stan Loo
----- Original Message -----
From: "Ben Petersen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 08, 2001 6:17 PM
Subject: RE: Capturing Duplicates
> I've been watching this thread and wondering what someone would
> do with a bunch of duplicate records, so I'll add my 2 cents...
>
> Del dup fro tblName
>
> ---- or ----
>
> (using some of that very nice code from Albert earlier)
>
> Create view (col1,Col2,col3) as +
> SELECT Col1,Col2,Col3 +
> FROM OriginalTable t1, +
> (SELECT Col1,Col2,Col3 FROM Original table +
> group by Col1,col2,col3 having count(*) > 1) as t2 +
> WHERE t2.Col1 = t1.Col1 AND +
> t2.Col2 = t1.Col2 AND t2.Col3 = t1.Col3
>
> With a view the dups are always there to check... or if the data is
> clean you'll know it because the view won't return anything. Or, just
> use the select statement from Albert's code and put it on the
> screen w/o creating tables/views.
>
> Of course, it's getting late and I may well be missing something
> <g>.
>
> Ben Petersen
>
>
> >
> > >===== Original Message From [EMAIL PROTECTED] =====
> > >Now that you have the #'s of each duplicates, they can be expanded to
another
> > >table to exactly represent the original duplicates using declare cursor
and
> > an
> > >IF...ENDIF loop.
> > >RRR
> > >
> > >> suredata wrote:
> > >>
> > >> Thank you Bill, Ron, Dennis, Phil, and Albert for responding.
Apparently I
> > >> did not make myself clear enough as to what I want.
> > >>
> > >> I need to save all duplicate rows to a new table or file, not just
knowing
> > >> which rows in the original table have one or more duplicates. In
other
> > >> words, if a row in the original table has 5 duplicates (or 6
identical rows
> > >> in the table), I want to save these five duplicates as separate rows
in a
> > new
> > >> table. This new table would contain nothing but all duplicates in
the
> > >> original table. Is there an easy way to achieve this?
> > >>
> > >> I hope this is clearer. Thanks again
> > >>
> > >> Stan Loo
> >
>
>
