Xref: alice comp.databases.oracle.server:56446
Path: alice!news-feed.fnsi.net!cyclone.i1.net!newsfeed.enteract.com!hermes.visi.com!news-out.visi.com!cam-news-hub1.bbnplanet.com!atl-news-feed1.bbnplanet.com!news.gtei.net!news.ipass.net!not-for-mail
From: "David Sisk" <davesisk@ipass.net>
Newsgroups: comp.databases.oracle.server
References: <378281FC.FF6D8701@globalserve.net>
Subject: Re: How To List Duplicate Records ?..
Lines: 59
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <ulUg3.32$w3.97@news.ipass.net>
Date: Wed, 7 Jul 1999 22:58:45 -0400
X-Complaints-To: abuse@ipass.net
X-Trace: news.ipass.net 931402202 209.170.133.235 (Wed, 07 Jul 1999 22:50:02 EDT)
NNTP-Posting-Date: Wed, 07 Jul 1999 22:50:02 EDT
Organization: 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@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@globalserve.net
>