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.