Re: How To List Duplicate Records ??

Not the neatest solution but I think it will work.. /* Create an aggregate table */

create table junk
as
select cust_num,cust_id,count(*)
from
customer
where cust_current_flag='Y'
group by cust_num,cust_id;

/* Eliminate non-duplicates */

create table junk1
as
select cust_num ,count(*)
from junk
group by cust_num
having count(*) > 1

/* List those records where the cust_num appears more than once */

select cust_num,cust_id from junk
where cust_num in (select cust_num from junk1)

Angelica Veron wrote in message <3782829B.E94865AE_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 Tue Jul 06 1999 - 16:46:59 CDT