regarding peculiar error

vijaykumar · Post by **vijaykumar** » Mon Dec 18, 2006 12:41 pm

hi gurus,
iam doing join of 2 tables in my source stage.
its compiling and running without any errors, but its not loading in the target database.
plz help me, iam getting warning.
[DataStage][SQL Client][ODBC][Oracle][ODBC][Ora]ORA-00001: unique constraint (SCOTT.SYS_C001342) violated.
plz help me ,

cheers;
vijay

johnthomas · Post by **johnthomas** » Mon Dec 18, 2006 12:47 pm

This error is because you are Appending data to a table which has the same key values defined in the constraint .If you truncate the target table and load the data this error should not occur

vijaykumar · Post by **vijaykumar** » Mon Dec 18, 2006 12:58 pm

Hi gurus,
THANKS VERY MUCH.

i changed my update action to- clear the table and then insert rows.
iam not getting any error.

cheers;
vijay

chulett · Post by **chulett** » Mon Dec 18, 2006 1:14 pm

johnthomas wrote:If you truncate the target table and load the data this error should not occur

I'd be careful about giving advise like this without qualifying it, especially for a newbie. We don't want people going around truncating tables just to solve unique constraint errors... unless, of course, that's appropriate for their particular situation.

vijaykumar wrote:i changed my update action to- clear the table and then insert rows. iam not getting any error.

You do realize why that is, yes? You removed all records from the target table then loaded your set - and there were no duplicates in your source data. Hopefully that's what you intended to happen, each run would entirely replace whatever was there with the current load's data.

It's only a SCOTT table, I know, but still...

DSguru2B · Post by **DSguru2B** » Mon Dec 18, 2006 1:41 pm

Ouch. I really hope that truncating the target was really what he wanted. Also, clear table and then insert will take a lot of time if target table is huge. You might want to re-consider this approach.