Re: How To List Duplicate Records ?..

From: Satish <somasatish_at_yahoo.com>
Date: 6 Jul 1999 17:39:15 -0500
Message-ID: <37827783@discussions>

I can give u the hint... if u are good SQL writer...

You try using correlated sub query with equi join in the inner query...

i.e select * from customer AA where rowid <>(select rowid from cusotmer bb where
aa.custnumb = bb.custnum) and
cust_current_flag ='Y'.

Do the search by using rowid... b'cos rowid is more faster than any other query search compared to 15 different types of search indexes... hope this should help u...

Satish

Angelica Veron <Faro_at_globalserve.net> wrote:
>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 Tue Jul 06 1999 - 17:39:15 CDT