Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Using case statement in update query in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ankita.maheswari11

New User


Joined: 20 Aug 2011
Posts: 10
Location: India

PostPosted: Tue Sep 20, 2011 12:33 pm    Post subject: Using case statement in update query in DB2
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 20, 2011 2:50 pm    Post subject:
Reply with quote

Quote:
I a getting a SQL error.


i am getting a stomach-ache from laughing so loud.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1722
Location: UK

PostPosted: Tue Sep 20, 2011 2:59 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Tue Sep 20, 2011 3:02 pm    Post subject: Reply to: Using case statement in update query in DB2
Reply with quote

Quote:
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';


try this
Code:

UPDATE IMSX01.BEV04001_DIAGNOSIS
 SET a = CASE
 WHEN b = '23202' and END_DT = '9999-12-31' and EFF_DT = '2006-10-01' THEN  '2'
 WHEN b = 'V1368' and END_DT = '9999-12-31' and EFF_DT = '2006-10-01' THEN  '1'
 END
Back to top
View user's profile Send private message
ankita.maheswari11

New User


Joined: 20 Aug 2011
Posts: 10
Location: India

PostPosted: Tue Sep 20, 2011 3:38 pm    Post subject: Reply to: Using case statement in update query in DB2
Reply with quote

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...
Back to top
View user's profile Send private message
ankita.maheswari11

New User


Joined: 20 Aug 2011
Posts: 10
Location: India

PostPosted: Tue Sep 20, 2011 4:14 pm    Post subject: Reply to: Using case statement in update query in DB2
Reply with quote

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.
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Tue Sep 20, 2011 4:18 pm    Post subject:
Reply with quote

Good to hear that icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us