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: 6967
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: 1788
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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