DSXchange

Hi,
I have 6 columns in Table.No primary keys.
But unique index is created on first 5 columns.

I tried to update the table by taking these 5 index columns as key columns.
2 index columns have null values.

I have taken Oracle connector for Update.
Job went fine with no warnings.But record is not updated.

Please help me to sort out this issue

Thanks in advance,
srini

Hi,

additional information.
Reject error code:0
Reject error text :row not updated - update mode

Thanks,
Srini

Even though you have a unique index, you cannot have a PK over those fields because Primary Keys don't allow nullable fields. And you can't simply pass NULL to those fields and expect an equality check to work.

You'll need to provide your own update DML to handle that. Simplest way I've found is to use NVL() to any possible nulls to an "in-band" value - something not found in the data. And you need to do it on both sides for each nullable field. For example, let's say A is one of your problem children, is a number and negative numbers are not possible:

WHERE NVL(source.A,-1) = NVL(target.A,-1)

With that in the DML, things should work fine.

Thanks Chulett.

Is there any alternate way for this scenario?

Thanks,
srini

you should have a surrogate key for updates in such cases where you can replace the nulls with some value you are not expecting in the data such as 0, -9999 or ~, and then do a lookup and send records to update based on surrogate key you got from there.

Short answer: no. Anything you do that needs to use that composite index will involve NVL(). Nature of the beast.