Page 1 of 1
COALESCE handling null value
Posted: Tue Oct 03, 2006 3:37 am
by simon
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
Posted: Tue Oct 03, 2006 3:29 pm
by ray.wurlod
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.
Posted: Tue Oct 03, 2006 8:56 pm
by kumar_s
What is not working? What column you are expecting to be updated? Under which condition?
Posted: Tue Oct 03, 2006 8:57 pm
by simon
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
Posted: Tue Oct 03, 2006 11:40 pm
by ray.wurlod
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.
Posted: Wed Oct 04, 2006 2:58 am
by simon
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)
Posted: Wed Oct 04, 2006 8:02 am
by kumar_s
Hope this was your initial requirement, to update Null values and not to update the Not null values.