where condition in update statement issue

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
India2000
Participant
Posts: 274
Joined: Sun Aug 22, 2010 11:07 am

where condition in update statement issue

Post by India2000 »

Hi
I have a scenario where I want to update a table for records with date column is null

If I use generated query with date as a key column, job doesn't update anything in the table.

I think custom query doesn't accept Date is null and job fails if I use it.


Could any of you please let me know how to handle this situation?

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

Post by chulett »

A bit lost. Why don't we start with you posting the generated SQL? If you are passing in a value of NULL to a where clause that says where your "date column" *equals* that passed in value, then yes it will never work.

What database is this?
-craig

"You can never have too many knives" -- Logan Nine Fingers
India2000
Participant
Posts: 274
Joined: Sun Aug 22, 2010 11:07 am

Post by India2000 »

DB2 ..tried ...UPDATE SCHEMA.TABLE SET COL1 = ORCHESTRATE.COL1, COL2 = ORCHESTRATE.COL_2 WHERE ( KeY_COL1 = ORCHESTRATE.KeY_COL1 AND DATE=ORCHESTRATE.DATE)



UPDATE SCHEMA.TABLE SET COL1 = ORCHESTRATE.COL1, COL2 = ORCHESTRATE.COL_2 WHERE ( KeY_COL1 = ORCHESTRATE.KeY_COL1 AND DATE IS NULL)
India2000
Participant
Posts: 274
Joined: Sun Aug 22, 2010 11:07 am

Post by India2000 »

I used NVL to handle this scenario. andit worked. thanks
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Right. Exactly.
-craig

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