On Apr 23, 8:31 pm, Le Tubs <dlaw...@[EMAIL PROTECTED]
> wrote:
> Hi
>
> Apologies if this gets posted twice
>
> I am relatively new to PL/SQL and want to do is find a list of
> duplicate records, then recording the duplicate c_rec id's in some
> sort of structure which I can extract at a later date
> this is what I have got so far ....
>
> TYPE DupID as Table of (
>
> wID NUMBER;
> AID NUMBER;
> BID Number;
> )
>
> select ???? = A.PAYROLL_ID, A.C_REC, B.C_REC from FMAPPING A where
> (SELECT * FROM FMAPPING B where
> A.PAYROLL_ID = B.PAYROLL_ID AND A.C_REC <>
B.C_REC);
>
> -- at this point can just extract each row, and I will have access to
> the payroll_id, and the unique c_rec
>
> So is this correct, but how do I load the DupID table with the
> contents of the select statment (I am assuming that the sql is
> correct)
>
> I would ideally like to learn what I'm doing right and wrong, so
> ideally what I'm after are pointers to reference sites, decent example
> sites or books rather than just the answer. Any tips or pointers ectra
> will be greatly appreciated.
>
> Thanking you in advance for your time and consideration.
>
> LeTubs
DECLARE
TYPE DupIDRow IS RECORD(
wID FMAPPING.PAYROLL_ID%TYPE,
AID FMAPPING.C_REC%TYPE,
BID FMAPPING.C_REC%TYPE
);
TYPE DupIDTab IS TABLE OF DupIDRow INDEX BY BINARY_INTEGER;
DupIDs DupIDTab;
BEGIN
SELECT A.PAYROLL_ID, A.C_REC, B.C_REC
BULK COLLECT INTO DupIDs
FROM FMAPPING A, FMAPPING B
WHERE A.PAYROLL_ID = B.PAYROLL_ID AND A.C_REC <> B.C_REC;
...
END;
Note that if the number of rows returned by the query is large, you
may want to use LIMIT clause of the BULK COLLECT and iteratively
process collected results in a loop to conserve memory. Also note that
your query will return two rows for each pair of duplicates. For
example,
(payroll_id,c_rec)
(1,1)
(1,2)
will return
1,1,2
1,2,1
and
(1,1)
(1,2)
(1,3)
will return 6 rows:
1,1,2
1,1,3
1,2,1
1,2,3
1,3,1
1,3,2
Not sure this is what you really need. :) If you only want the
"duplicate" rows themselves, you can do it this way:
SELECT * FROM FMAPPING
WHERE PAYROLL_ID IN
(SELECT PAYROLL_ID FROM FMAPPING
GROUP BY PAYROLL_ID HAVING COUNT(*) > 1)
and if you only need PAYROLL_IDs that have duplicates, you can use
just the query inside the IN ().
Another way to approach the problem (probably more efficient and
definitely simpler):
CREATE TABLE MY_DUPLICATES_TABLE
AS
SELECT A.PAYROLL_ID WID, A.C_REC AID, B.C_REC BID
FROM FMAPPING A, FMAPPING B
WHERE A.PAYROLL_ID = B.PAYROLL_ID AND A.C_REC <> B.C_REC;
No PL/SQL required, single SQL statement does it all, you can then
process the results any way you please. If this is going to be a
frequent operation and you don't need to keep the query results
between sessions, you can make MY_DUPLICATES_TABLE a GLOBAL TEM****ARY
table with either ON COMMIT PRESERVE ROWS if you want the data to
survive commits in session, or ON COMMIT DELETE ROWS if the data is
truly tem****ary.
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
|
