Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How To List Duplicate Records ?..

Re: How To List Duplicate Records ?..

From: David Sisk <davesisk_at_ipass.net>
Date: Wed, 7 Jul 1999 22:58:45 -0400
Message-ID: <ulUg3.32$w3.97@news.ipass.net>

  1. Create a UNIQUE constraint on the columns in which you wish to check for duplication, and specify the EXCEPTIONS INTO clause. The constraint won't enable (obviously), but it will put the rowid if all the of duplicates into the exceptions table you specify.

Good luck,

--
David C. Sisk
The Unofficial ORACLE on NT site
http://www.ipass.net/~davesisk/oont.htm

Angelica Veron wrote in message <378281FC.FF6D8701_at_globalserve.net>...
>Hello Everyone,
>
>I have the following table called Customer....
>
>SQL> desc customer;
>
> Name Null? Type
> ------------------------------- -------- ----
> CUST_ID NOT NULL NUMBER(10)
> RELMGR_ID NUMBER(15)
> HH_HOUSEHOLD_ID NOT NULL NUMBER(10)
> CUST_HIST_ID NOT NULL NUMBER(15)
> CUST_NUM NOT NULL VARCHAR2(10)
> CUST_FIRST_NAME VARCHAR2(40)
> CUST_LAST_NAME VARCHAR2(40)
> CUST_REL_MGR_ASSIGN_DATE DATE
> CUST_RB_REL_MGR_FLAG CHAR(1)
> CUST_BIRTHDATE DATE
> CUSTOMER_ADD_RUN NOT NULL NUMBER
> CUSTOMER_UPD_RUN NOT NULL NUMBER
> CUST_CURRENT_FLAG CHAR(1)
> BEGIN_DATE DATE
>
>
>SQL> select count(*) from customer;
>
> COUNT(*)
>---------
> 1311351
>
>
> This table was recently created, and I need to check for the
>existance of records with differing CUST_ID's but have the same
>CUST_NUM. Overall, I need to locate all records which have the same
>CUST_NUM, but different CUST_ID's , and the CUST_CURRENT_FLAG has to
>equal 'Y' (meaning they are current). Does anyone know which query will
>list such duplicate records for me ??... any help would be greatly
>appreciated, thank you for your time.
>
> Friendly Regards,
> Angelica Veron
> Faro_at_globalserve.net
>
Received on Wed Jul 07 1999 - 21:58:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US