Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Update with Case

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

New User


Joined: 29 Jun 2010
Posts: 6
Location: Chennai

PostPosted: Wed Aug 10, 2011 12:27 pm    Post subject: Update with Case
Reply with quote

Hi,

I have the following query :

UPDATE TableA
SET C1 =
CASE
WHEN VAR ='Y' THEN 10
END,
C2 =
CASE
WHEN VAR ='Y' THEN 20
END
WHERE C4 = 2


If my VAR <> 'Y', then i need to skip my update for the columns C1 and C2. My columns are Not Nullable. My update throws a -407 due to this. Can this be achieved in any other manner?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Aug 10, 2011 12:37 pm    Post subject:
Reply with quote

seems a little ass-backwards to involve the CASE.

why not

Code:

UPDATE TableA
   SET C1  = 10
      ,C2  = 20
WHERE  C4  = 2
  AND  VAR = 'Y'


and learn to use BBCode
Back to top
View user's profile Send private message
Karthikeyan Venkatadri

New User


Joined: 29 Jun 2010
Posts: 6
Location: Chennai

PostPosted: Wed Aug 10, 2011 12:43 pm    Post subject: Reply to: Update with Case
Reply with quote

Hi Dick,

Sorry for the confusion. VAR is not a column. Its a working storage variable. Depending on the value of the variable i need to update my columns.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 10, 2011 12:48 pm    Post subject:
Reply with quote

Hello and welcome to the forum,

Then move the VAR test outside the sql. . .

Code:
IF VAR = Y then
   UPDATE. . . . ?
as DBZ suggested?
Back to top
View user's profile Send private message
Karthikeyan Venkatadri

New User


Joined: 29 Jun 2010
Posts: 6
Location: Chennai

PostPosted: Wed Aug 10, 2011 1:01 pm    Post subject: Reply to: Update with Case
Reply with quote

Sorry again. I think am messing things a bit. These are 2 different variables.

UPDATE TableA
SET C1 =
CASE
WHEN VAR-1 ='Y' THEN 10
END,
C2 =
CASE
WHEN VAR-2 ='Y' THEN 20
END
WHERE C4 = 2

My exact scenario : I have 7 different variables like Var-1 and var-2. Depending on the value of each variable i need to update 7 coumns. To avoid multiple queries for each combination of the 7 variables, i was trying to club everything into one query. Is there a way i can do it at all?? or do i need to write separate update queries for each combination?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Aug 10, 2011 1:08 pm    Post subject:
Reply with quote

if VAR is always the same VAR then dbz solution still stands , or even solve it in your program :
Code:
if var = Y then
   exec sql  update..
   end-exec
end-if

if you somehow have these VARs per column you'll need to do something like this
Code:
...
 C1 = CASE WHEN :VAR-c1 ='Y' THEN 10 else C1 END
,C2 = CASE WHEN :VAR-c2 ='Y' THEN 20 else C2 END
...
Back to top
View user's profile Send private message
Karthikeyan Venkatadri

New User


Joined: 29 Jun 2010
Posts: 6
Location: Chennai

PostPosted: Wed Aug 10, 2011 1:13 pm    Post subject: Reply to: Update with Case
Reply with quote

Hi GuyC,

Thank a lot. My variables are per column. The second solution worked.
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
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 IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us