Re: Newbie PL/SQL question

How about something like this:

select s1.id,s2.id from sample s1, sample s2 where s1.rowid!= s2.rowid and s1.dnumber=s2.dnumber and s1.year=s2.year and s1.num=s2.num and s1.dte=s2.dts and s1.rowid<s2.rowid;

Then you will know the duplicate values (dnumber,year,num,dte) but not repeated values. You could alos use group by but then you would have a hard time knowing the two id numbers. Basically, you are joining the table to itself; it should be faster than the plsql method.

Jim

Anne Hanson <ahanson_at_fas.harvard.edu> wrote in message news:37FA61AB.EC801ADB_at_fas.harvard.edu...
> This is a long question, so thanks in advance if you are able to take
> time to help. I'm learning PL/SQL at the same time as I'm trying to
> create a solution for identifying rows with duplicate values.
>
> I have a table called SAMPLE in which some rows have duplicate values in
> all except the PK (ID) column. For example:
>
> ID DNUMBER YEAR NUM DTE
> ---------- ---------- ---------- ---------- --------------------
> 50000 4561 1997 2 07-DEC-41
> 50001 4561 1997 1 07-DEC-41
> 50003 4692 1996 1 16-SEP-96
> 50005 4692 1996 2 07-DEC-41
> 50007 4692 1997 1 07-DEC-41
> 50009 4692 1997 1 07-DEC-41
> 50010 4701 1998 1 12-MAY-98
> 50012 4701 1996 1 16-SEP-96
> 50014 4701 1996 2 07-DEC-41
> 50016 4701 1996 2 07-DEC-41
> ...
>
> I am writing a program to return the IDs of every row where the values
> for DNUMBER, YEAR, NUM and DTE are duplicates of another row. In the
> records above, I would want to return ID#s 50007, 50009, 50014, 50016.
> Duplicate values will not always directly follow the values they repeat.
> I also want this program to be generalizable to other tables in the
> future.
>
> My current program (has errors) looks like this:
>
> SET serveroutput on;
> DECLARE
> CURSOR sample_curs IS
> select DNUMBER, YEAR, NUM, DTE from SAMPLE;
> row_var sample_curs%ROWTYPE;
> count_var number(2);
> id_var SAMPLE.ID%TYPE;
>
> BEGIN
>
> OPEN sample_curs;
> FETCH sample_curs INTO row_var;
> WHILE (sample_curs%FOUND) LOOP
>
> SELECT count (*) into count_var from SAMPLE s where --How many
> matches?
> s.DNUMBER||s.YEAR||s.NUM||s.DTE =
> row_var.DNUMBER||row_var.YEAR||row_var.NUM||row_var.DTE;
> IF count_var > 1 THEN
> SELECT s.ID into ID_var from SAMPLE s where --if > 1, get SAMPLE.ID
> of dups
> s.DNUMBER||s.YEAR||s.NUM||s.DTE =
> row_var.DNUMBER||row_var.YEAR||row_var.NUM||row_var.DTE;
> dbms_output.put_line('Record '||ID_var||' is duplicate.');
> END IF;
> FETCH sample_curs into row_var;
> END LOOP;
> CLOSE sample_curs;
> END;
> /
>
> The error message I am currently getting is:
>
> DECLARE
> *
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at line 18
>
> What's the best way to output the IDs of the rows with dup columns? I've
> tried a number of
> things and currently am low on inspiration. Or is there a better way to
> approach this? Thanks for any suggestions,
>
> Anne
>
Received on Tue Oct 05 1999 - 00:00:00 CDT