Re: How To List Duplicate Records ??

Angelica, try this:

select * from customer
where cust_num in (select cust_num from customer group by cust_num having count(*) > 1)
and cust_current_flag = 'Y'

this assumes that table customer is a base table, i.e. that there should be one and only one row for each cust_id.

HTH,
John.

Angelica Veron 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

--
John Early jpe1_at_lehigh.edu x85066 Senior Programmer, Enterprise Systems Implementation Lehigh University, Bethlehem, Pennsylvania qotd: "Whereof one cannot speak, thereof one must be silent." (Wittgenstein) Received on Wed Jul 07 1999 - 09:39:40 CDT