Getting Unique Constraints while copying from 1 DB to other.

I am trying to copy data from PRO to ACP for some testing purpose....
but am getting unique constraint even though there dosent exist any dupilcate records in PRO...
Because both ACP & PRO have PK set of columns same...i belive i should not get this error....i mean it dosent make sense...

PS below

Code:

---

ACP side

WW15:W_DBA>copy from wwciw_dba@ww04 to wwciw_dba@ww15 insert WWCIW_SAP.ytcurr using select * from wwciw_sap.ytcurr
FROM*********
TO*****

Array fetch/bind size is 1. (arraysize is 1)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA-00001: unique constraint (WWCIW_SAP.SYS_C001519) violated


WW15:W_DBA>select * from dba_cons_columns where constraint_name='SYS_C001519';

OWNER                          CONSTRAINT_NAME                TABLE_NAME                    COLUMN_NAME       
------------------------------ ------------------------------ ------------------------------ -------
WWCIW_SAP                      SYS_C001519                    YTCURR                        KURST             
WWCIW_SAP                      SYS_C001519                    YTCURR                        FCURR             
WWCIW_SAP                      SYS_C001519                    YTCURR                        TCURR             
WWCIW_SAP                      SYS_C001519                    YTCURR                        GDATU

---

Code:

---

PRO Side

WW04:W_DBA>select * from dba_cons_columns where constraint_name='SYS_C001519';

OWNER                          CONSTRAINT_NAME                TABLE_NAME                    COLUMN_NAME       
------------------------------ ------------------------------ ------------------------------ ------------
WWCIW_SAP                      SYS_C001519                    YTCURR                        KURST             
WWCIW_SAP                      SYS_C001519                    YTCURR                        FCURR             
WWCIW_SAP                      SYS_C001519                    YTCURR                        TCURR             
WWCIW_SAP                      SYS_C001519                    YTCURR                        GDATU             

WW04:W_DBA> select
  2  KURST,
  3  FCURR,
  4  TCURR,
  5  GDATU
  6  from wwciw_sap.ytcurr having count(*) > 1
  7  group by
  8  KURST,
  9  FCURR,
 10  TCURR,
 11  GDATU;

no rows selected

---

Can any body throw some light??

am confused...

Thanks
Abhay.

If u are using any sequences better check this from metalink

ID:Note:125720.1

No sequences are not invloved here.

Thanks anyway

Abhay.

try this query
select
KURST,FCURR,TCURR,GDATU,count(1)
from wwciw_sap.ytcurr
group by
KURST,FCURR,TCURR,GDATU
having count(1) > 1;

I guess your WWCIW_SAP.ytcurr table in ACP side is not empty before you start copying. So some records with the same unique constraint values that you are trying to copy from the other table obviously allready exists in your destination table.

Quote:

---

Originally posted by jmodic
I guess your WWCIW_SAP.ytcurr table in ACP side is not empty before you start copying. So some records with the same unique constraint values that you are trying to copy from the other table obviously allready exists in your destination table.

---

Jurij :

Before copy, we truncate the table in ACP....so no question of having any records....

Quote:

---

try this query
select
KURST,FCURR,TCURR,GDATU,count(1)
from wwciw_sap.ytcurr
group by
KURST,FCURR,TCURR,GDATU
having count(1) > 1;

---

Mallika,

Indeed by this query we get a dupilcate records....as i heard from my colleague that in PRO PK is with no validate option and can have dulicates.....

Code:

---

WW15:W_DBA> select kurst, fcurr, tcurr, gdatu, count(1) from 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

---

Thnaks....but again why will the query with "having option before group by clause" will not work???

Abhay.

Quote:

---

Originally posted by abhaysk
Jurij :
Thnaks....but again why will the query with "having option before group by clause" will not work???

Abhay.

---

Abhay,
HAVING clause is used with GROUP BY clause and in the syntex it should be after GROUP BY.

having is like a predicate for aggreate functions

Quote:

---

Originally posted by SANJAY_G
Abhay,
HAVING clause is used with GROUP BY clause and in the syntex it should be after GROUP BY.

---

Thas Correct sanjay but i think after 7i it should not be a case...
Our PRO is 8i.

Its irrelevent where ever u have Having clause, result shud be same...


PS below from TEST DB (8i)

Code:

---

SQL> select * from tabl;

                  ID
--------------------
                  1
                  1
                  2
                  2
                  5
                  7

6 rows selected.

SQL> select id,count(1) from tabl having count(1)> 1 group by id;

                  ID            COUNT(1)
-------------------- --------------------
                  1                    2
                  2                    2

SQL> select id,count(1) from tabl group by id having count(1)> 1;

                  ID            COUNT(1)
-------------------- --------------------
                  1                    2
                  2                    2

---

Thanks
Abhay.

Yes I was wrong, it can be before or after GROUP BY. Just checked, it is same on 7.3.4 as well.