DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Unique Constraints With Out having duplicate records. Or the other way round.

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Unique Constraints With Out having duplicate records. Or the other way round.

    Hi All,

    Please read this post fully ( Appriciate Ur Patientence )

    PK_YTCURR exists on the coulmns (kurst, fcurr, tcurr, gdatu) of table YTCURR in both PRO and ACP.

    Code:
    WWCIW_DBA@WW15> truncate table wwciw_sap.ytcurr;
    
    Table truncated.
    
    WWCIW_DBA@WW15> copy from wwciw_dba@ww04 to wwciw_dba@ww15-
    > insert ytcurr-
    > using-
    > select * from ytcurr
    FROM
    TO
    
    Array fetch/bind size is 5000. (arraysize is 5000)
    Will commit after every 5 array binds. (copycommit is 5)
    Maximum long size is 80. (long is 80)
    SQLRCN in cpytbl failed: -1075
    SQLRCN in cpyyerr failed: -1075
    
    ERROR:
    ORA-00001: unique constraint (WWCIW_SAP.PK_YTCURR) violated
    
    
    WWCIW_DBA@WW15> select name from v$database;
    
    NAME
    ---------
    WW15
    
    WWCIW_DBA@WW15> truncate table wwciw_sap.ytcurr;
    
    Table truncated.
    
    WWCIW_DBA@WW15> insert into ytcurr select * from ytcurr@ww04;
    
    594951 rows created.
    
    -- Now it dosent error, Strange
    
    WWCIW_DBA@WW15> commit;
    
    Commit complete.
    Now i use some temp table to check if really duplicates exists.

    Code:
    WWCIW_DBA@WW15>truncate table temp_ytcurr;
    
    Table truncated.
    
    WWCIW_DBA@WW15>copy from wwciw_dba@ww04 to wwciw_dba@ww15-
    > insert temp_ytcurr-
    > using-
    > select * from ytcurr
    FROM
    TO
    
    Array fetch/bind size is 5000. (arraysize is 5000)
    Will commit after every 5 array binds. (copycommit is 5)
    Maximum long size is 80. (long is 80)
    SQLRCN in cpytbl failed: -1075
       594951 rows selected from wwciw_dba@ww04.
       594951 rows inserted into TEMP_YTCURR.
       594951 rows committed into TEMP_YTCURR at wwciw_dba@ww15.
    
    WWCIW_DBA@WW15>select kurst, fcurr, tcurr, gdatu, count(1) from temp_ytcurr
      2  group by kurst, fcurr, tcurr, gdatu
      3  having count(1) > 1
      4  /
    
    KURS FCURR TCURR GDATU      COUNT(1)
    ---- ----- ----- -------- ----------
    M    GRD   USD   19990320          2
    P    USD   PTE   19960901          2
    
    -- Now I Load into YTCURR(after truncating it) excluding these records
    
    WWCIW_DBA@WW15>Truncate table TEMP_YTCURR;
    
    Table truncated.
    
    WWCIW_DBA@WW15>insert into TEMP_YTCURR select * from YTCURR@ww04;
    
    594951 rows created.
    
    WWCIW_DBA@WW15>select kurst, fcurr, tcurr, gdatu, count(1) from temp_ytcurr
      2  group by kurst, fcurr, tcurr, gdatu
      3  having count(1) > 1
      4  /
    
    no rows selected
    
    -- Now begins the interesting part.
    
    WWCIW_DBA@WW15> ed
    Wrote file afiedt.buf
    
      1  Select kurst, fcurr, tcurr, gdatu from
      2  (
      3  select ltrim(rtrim(t.KURST)) Kurst, ltrim(rtrim(t.FCURR)) FCURR, ltrim(rtrim(t.TCURR)) TCURR, ltrim(rtrim(t.GDATU)) GDATU
      4  from temp_ytcurr t
      5  minus
      6  select ltrim(rtrim(y.KURST)) Kurst, ltrim(rtrim(y.FCURR)) FCURR, ltrim(rtrim(y.TCURR)) TCURR, ltrim(rtrim(y.GDATU)) GDATU
      7  from ytcurr y
      8* )
    WWCIW_DBA@WW15> /
    
    KURS FCURR TCURR GDATU
    ---- ----- ----- --------
    M    GRD   USD   19990320
    P    USD   PTE   19960901
    
    -- AS Expected.
    
    WWCIW_DBA@WW15> ed
    Wrote file afiedt.buf
    
      1  Select * from ytcurr where (kurst, fcurr, tcurr, gdatu) in
      2  (
      3  Select kurst, fcurr, tcurr, gdatu from
      4  (
      5  select ltrim(rtrim(t.KURST)) Kurst, ltrim(rtrim(t.FCURR)) FCURR, ltrim(rtrim(t.TCURR)) TCURR, ltrim(rtrim(t.GDATU)) GDATU
      6  from temp_ytcurr t
      7  minus
      8  select ltrim(rtrim(y.KURST)) Kurst, ltrim(rtrim(y.FCURR)) FCURR, ltrim(rtrim(y.TCURR)) TCURR, ltrim(rtrim(y.GDATU)) GDATU
      9  from ytcurr y
     10  )
     11* )
     12  /
    
    no rows selected
    
    -- AS Expected.
    
    WWCIW_DBA@WW15> ed
    Wrote file afiedt.buf
    
      1  Select * from ytcurr where (ltrim(rtrim(kurst)), ltrim(rtrim(fcurr)), ltrim(rtrim(tcurr)), ltrim(rtrim(gdatu))) in
      2  (
      3  Select kurst, fcurr, tcurr, gdatu from
      4  (
      5  select ltrim(rtrim(t.KURST)) Kurst, ltrim(rtrim(t.FCURR)) FCURR, ltrim(rtrim(t.TCURR)) TCURR, ltrim(rtrim(t.GDATU)) GDATU
      6  from temp_ytcurr t
      7  minus
      8  select ltrim(rtrim(y.KURST)) Kurst, ltrim(rtrim(y.FCURR)) FCURR, ltrim(rtrim(y.TCURR)) TCURR, ltrim(rtrim(y.GDATU)) GDATU
      9  from ytcurr y
     10  )
     11* )
    WWCIW_DBA@WW15> /
    
    no rows selected
    
    -- Can expect it.
    
    WWCIW_DBA@WW15> select * from ytcurr where kurst like '%M%' and FCURR like '%GRD%' and TCURR like '%USD%'
      2  and GDATU like '%19990320%';
    
    MAN KURS FCURR TCURR GDATU         UKURS      FFACT      TFACT CYDT     CYTM   XTFLNM
    --- ---- ----- ----- -------- ---------- ---------- ---------- -------- ------ ----------
    155 M    GRD   USD   19990320     .00339          1          1 19990319 235900 CENA01
    155 MV   GRD   USD   19990320     .00339          1          1 19990319 235900 CENA01
    
    -- Jus see the first record it indeed exists in main table YTCURR. This is were i am confused
    
    WWCIW_DBA@WW15> ed
    Wrote file afiedt.buf
    
      1  select * from temp_ytcurr where kurst like '%M%' and FCURR like '%GRD%' and TCURR like '%USD%'
      2* and GDATU like '%19990320%'
    WWCIW_DBA@WW15> /
    
    MAN KURS FCURR TCURR GDATU         UKURS      FFACT      TFACT CYDT     CYTM   XTFLNM
    --- ---- ----- ----- -------- ---------- ---------- ---------- -------- ------ ----------
    155 M    GRD   USD   19990320     .00339          1          1 19990319 235900 CENA01
    155 MV   GRD   USD   19990320     .00339          1          1 19990319 235900 CENA01
    
    -- IN Temp Table we have same records. More confused
    I suspect some special characters to be present in a record, but again its not showing up with search by using like predicate '%--%'

    Please help


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Any inputs from any 1?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width