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.
