duplicates in outrownum

dnat · Post by **dnat** » Tue Sep 09, 2008 5:09 am

Hi,

We are doing a table refresh i.e we are loading the data from a sequential file to table. I have an id column in the table which should be unique. I am passing outrownum as the value for that. But the job is aborting with unique constraint violation. how is this possible.

i changed the job to take the id from the routine KeyMgtGetNextValue('n') and it is not giving any violation.

but i dont want to use this routine because i want the id to start from 1 everytime i load

can anyone help me with this

ray.wurlod · Post by **ray.wurlod** » Tue Sep 09, 2008 5:58 am

The target table already has rows in it. @OUTROWNUM always starts from 1. You need to determine the current maximum key value before your job processes any rows, perhaps loading it into a hashed file with a constant key, and add this value to @OUTROWNUM.

dnat · Post by **dnat** » Tue Sep 09, 2008 7:27 am

we are truncating the records in the table and then only loading it.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 09, 2008 7:34 am

@OUTROWNUM can never violate a unique constraint on a truncated table. Check for any other unique constraints that there may be. Check too that you are loading the column that you think you are with this sequence of integers - that is, verify that the design is doing what it is supposed to be doing. Look carefully at the error message; it should name the constraint, and you can then track it down in a more targeted fashion.