Update issue on Index columns:Oracle connector stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
ssreeni3
Participant
Posts: 29
Joined: Fri May 18, 2012 1:35 am

Update issue on Index columns:Oracle connector stage

Post by ssreeni3 »

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
ssreeni3
Participant
Posts: 29
Joined: Fri May 18, 2012 1:35 am

Post by ssreeni3 »

Hi,

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

Thanks,
Srini
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ssreeni3
Participant
Posts: 29
Joined: Fri May 18, 2012 1:35 am

Post by ssreeni3 »

Thanks Chulett.

Is there any alternate way for this scenario?

Thanks,
srini
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

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.
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Post Reply