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
 

 

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: 6966
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: 1278
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 Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts 2 vsam file compare and update a fiel... ABINAYATHULASI DFSORT/ICETOOL 6 Sun May 29, 2016 11:15 am


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