COALESCE handling null value

simon · Post by **simon** » Tue Oct 03, 2006 3:37 am

i have one problem when i try to update null value in "SERVER JOB" in "Datastage 7.5.1.A"

UPDATE SOR_DATA_SVC SET JOB_INST_ID=?,LAST_UDT_TMS=?,DSC=? WHERE DATA_SVC_TP=? AND DATA_SVC_CODE=? AND COALESCE(DATA_SVC_SUB_CODE,'')=COALESCE(?,'');

Null value can be updated after i put that update command but the "NOT NULL VALUE" can not be updated.

can anyone give me a hand

thx in advance

ray.wurlod · Post by **ray.wurlod** » Tue Oct 03, 2006 3:29 pm

Welcome aboard. :D
You would be far better off converting the NULL to "" within the job design. There is a built-in Transform called NullToEmpty exactly for that purpose.

kumar_s · Post by **kumar_s** » Tue Oct 03, 2006 8:56 pm

What is not working? What column you are expecting to be updated? Under which condition?

simon · Post by **simon** » Tue Oct 03, 2006 8:57 pm

thx Ray

i did try it but it still doesn't work.

i have the other solution, i put one more link from TRANSFORMER to db2
( one due with not null value and other do null value) but i feel so strange N my group dont' accept this solution.
i hope u can give more advise

thx in advance

ray.wurlod · Post by **ray.wurlod** » Tue Oct 03, 2006 11:40 pm

What is happening to make you say "it doesn't work"? That is not a particularly specific symptom description.

If you've used NullToEmpty to remove nulls from the column, also lose the second COALESCE from the sql statement. It's unnecessary.

simon · Post by **simon** » Wed Oct 04, 2006 2:58 am

it doesn't work same as my previous problmes (it only can be updated "NULL VALUE" when i put "COALESCE" but still not update "not null value" when i used "NullToEmpty".

It did work in Datastage XE verison but not wokring in Datastage EE verison (7.5.1.A)

kumar_s · Post by **kumar_s** » Wed Oct 04, 2006 8:02 am

Hope this was your initial requirement, to update Null values and not to update the Not null values.