Using case statement in update query in DB2

ankita.maheswari11 · New User Joined: 20 Aug 2011 Posts: 10 Location: India

Hi,

I have to create a update query in which i have to set a = '2' if b = '23202' and '1' if b = 'V1368', also i have to check for conditions where end_dt = '9999-12-31' and eff_dt = '2006-10-01'.

For this i want to use a case in update query, i have created a query but it is not working, please find the query below:

UPDATE IMSX01.BEV04001_DIAGNOSIS
SET a = CASE
WHEN b = '23202' THEN a = '2'
WHEN b = 'V1368' THEN a = '1'
END
WHERE END_DT = '9999-12-31'
AND EFF_DT = '2006-10-01';

Can some one please help me this? I a getting a SQL error.

dbzTHEdinosauer · Posted: Tue Sep 20, 2011 2:50 pm

Nic Clouston · Posted: Tue Sep 20, 2011 2:59 pm

If you provide information regarding the SQL error )such as the message and message number and what the manual says about the message) maybe Dick will be able to stop laughing and start to help you.

Dsingh29 · Active User Joined: 16 Dec 2008 Posts: 132 Location: IBM

ankita.maheswari11 · New User Joined: 20 Aug 2011 Posts: 10 Location: India

Tried below:

UPDATE IMSX01.BEV04001_DIAGNOSIS
SET a =
CASE
WHEN b = 'V1368' AND END_DT = '9999-12-31'
AND EFF_DT = '2006-10-01' THEN 1
WHEN b = '23202' AND END_DT = '9999-12-31'
AND EFF_DT = '2006-10-01' THEN 2
END

Getting below error:

"Column cannot contain a NULL value." Their is no number coming with this error message...

ankita.maheswari11 · New User Joined: 20 Aug 2011 Posts: 10 Location: India

Thanks for the help guys.

It is done, the problem was i was not using else part of case. but now i am using it and query is running fine.

Dsingh29 · Active User Joined: 16 Dec 2008 Posts: 132 Location: IBM

Good to hear that