Re: Table With Duplicate Records..

HI
create table temp_bcis_mf_monthly storage ...

as select distinct * from bcis_mf_monthly;

this will give you a table with unique records from previous table. HTH
pradhan;
Angelica Veron wrote in message <37A8CD29.E24A53CE_at_globalserve.net>...
>Hello to everyone,
>
> I have a problem concerning a table with duplicated records.
>
>SQL> desc bcis_mf_monthly
> Name Null? Type
> ------------------------------- -------- ----
> CIF_SRF VARCHAR2(15)
> LAST_NAME VARCHAR2(60)
> FIRST_NAME VARCHAR2(30)
> MIDDLE_INITIAL VARCHAR2(30)
> CIF_CUST_GRT VARCHAR2(8)
> HHLD_KEY VARCHAR2(13)
> COST_HHLD NUMBER(11)
> REVENUE_HHLD NUMBER(11)
> VALUE_HHLD NUMBER(11)
> RT_MATRIX_CODE VARCHAR2(2)
> ACCOUNT_NUM VARCHAR2(16)
> BRANCH VARCHAR2(4)
> PTYPE VARCHAR2(3)
> SECONDARY_NAME VARCHAR2(50)
> CARE_OF_ADDR VARCHAR2(50)
> CITY VARCHAR2(26)
> POSTAL_CODE VARCHAR2(10)
> PROVINCE_CODE VARCHAR2(10)
> SECONDARY_ADDR VARCHAR2(60)
> STREET_ADDRESS VARCHAR2(60)
> HHPORT_MGR_NM VARCHAR2(60)
> HTRUST_STAFF_NAME VARCHAR2(60)
> HHLD_SEGCD CHAR(1)
> CLIENT2_CD VARCHAR2(3)
> PORT_MGR_NAME VARCHAR2(60)
> TRUST_MGR_NM VARCHAR2(60)
> STYPE VARCHAR2(3)
> HH_HOUSEHOLD_ID NUMBER(10)
> SERVICE_ID NUMBER(10)
> CUST_ID NUMBER(10)
> BRANCH_ID NUMBER
>
>
>SQL>
> 1 select ACCOUNT_NUM, BRANCH_ID,
> 2 PTYPE, STYPE, HH_HOUSEHOLD_ID
> 3 from bcis_mf_monthly
> 4* where hh_household_id = '10243'
>SQL> /
>
>ACCOUNT_NUM BRANCH_ID PTY STY HH_HOUSEHOLD_ID
>---------------- --------------- --- --- ---------------
>0423478 111 GIC 3YR 10243
>0000000Z01191454 111 RSP GIC 10243
>0000000Z01191454 111 RSP MFW 10243
>0000000Z01191454 111 RSP MFX 10243
>0000000Z01191454 111 RSP MFX 10243
>0000000Z01191454 111 RSP GIX 10243
>0000000Z01191454 111 RSP GIX 10243
>0000000Z01191454 111 RSP GIX 10243
>0000000Z01191454 111 RSP GIX 10243
>0000000Z01191454 111 RSP GIX 10243
>0000000Z01191454 111 RSP GIX 10243
>0000000AB0040062 111 RSP MFW 10243
>0000000AB0040062 111 RSP MFX 10243
>0000000800002148 111 RSP GIC 10243
>0000000800002148 111 RSP MFW 10243
>0000000800002148 111 RSP MFX 10243
>0000000800002148 111 RSP MFX 10243
>0000000800002148 111 RSP GIX 10243
>0000000800002148 111 RSP GIX 10243
>0000000800002148 111 RSP GIX 10243
>0000000800002148 111 RSP GIX 10243
>
>ACCOUNT_NUM BRANCH_ID PTY STY HH_HOUSEHOLD_ID
>---------------- --------------- --- --- ---------------
>0000000800002148 111 RSP GIX 10243
>0000000800002148 111 RSP GIX 10243
>
>23 rows selected.
>
>
> ...this is just one hh_household_id being duplicated so many times in
>that table, but there are more hh_household_id's in that table that are
>also duplicated as can be seen below :
>
>SQL> select count(*) total, hh_household_id from bcis_mf_monthly
> having count(hh_household_id) > 1
> group by hh_household_id;
>
> TOTAL HH_HOUSEHOLD_ID
>--------------- ---------------
> 7 10167
> 9 10172
> 3 10192
> 4 10197
> 5 10203
> 3 10224
> 7 10240
> 23 10243
> 22 10244
>
>
> As you can see, the above table has duplicate records, and what I
>need is a PL/SQL script that would create a table with distinct records.
>However, there is no key in this table, and the distinction should be
>made on a combination of the ACCOUNT_NUM, BRANCH_ID, and PTYPE fields.
>That is as long as the new table has records with a different
>combination of these 3 fields then it is ok. Would anyone know or have
>such a code to create such a table ?. I would be extremely grateful for
>any kind of input. Thank you so much for your time.
>
>
> Yours Sincerely,
> Angelica Veron
> Faro_at_globalserve.net
>
>
Received on Thu Aug 05 1999 - 05:46:56 CEST