Re: Using SQL to check for duplicate records

I have gotten into doig the following.
Subselects sometimes just take "days".

CREATE TABLE QTEMP/WORKFILE
  ( IPROD DEC (10,0),
    RCDNBR DEC (11,0))
INSERT INTO QTEMP/WORKFILE
   SELECT IPROD FROM IIM
   GROUP BY IPROD HAVING COUNT(*)>0
SELECT A.IPROD, RRN(B)
   FROM  IIM A, IIM B
   WHERE A.IPROD = B.IPROD

Can't say I've seen anything faster. I think any
fast-performing solution for this situtation would create
some kind of intermediate temporary table (or access path)
anyway, this just makes it more explicit.

That will get you all the records where the key is
duplicate. If you just want the duplicates, (not the first
one) with RRN it's a one-take, but still a join:

SELECT A.IPROD, RRN(A)
   FROM IIM A, IIM B
   WHERE A.IPROD = B.IPROD
         AND RRN(A) > RRN(B)

- Alan

| By the way, the output I am looking for is this:
| ....+....1....+....2....+....3....+....4..
|   IPROD                       RRN ( A )
|   ---------------  --------------------
|   A                                  1
|   A                                  4
|  * * * * *  E N D  O F  D A T A  * * * * *