Update action with NULL Key

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
sid19
Participant
Posts: 64
Joined: Mon Jun 18, 2007 12:17 am
Location: kolkata

Update action with NULL Key

Post by sid19 »

Hi All,

We have a job where it updates a Netezza table through Netezza connector stage. We are using write mode as Update. In key column we have more than one fields and among them one is Nullable. In table we have NULL value for this field for few records. Whenever any row comes with NULL value it's not able to match with table null value and datastage is not updaing for these records

How do I update these records. Is there any option by which it can match NULL value.

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

Post by chulett »

NULL never equals another NULL as both are unknown values. Sounds to me like you'll need custom SQL where the keys either equal or are both NULL. Something like:

Code: Select all

and (X = Y or (X is null and Y is null))
Or you can play a different game to do the same thing all at once. Here is the Oracle version since I don't know Netezza, your mileage may vary. And you'd need to use a value that matches the datatype and isn't otherwise a valid value:

Code: Select all

and NVL(X,-1) = NVL(Y,-1)
-craig

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