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
 

 

IF-ELSE/CASE logic in a UPDATE SQL

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

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Tue Feb 25, 2014 7:46 pm    Post subject: IF-ELSE/CASE logic in a UPDATE SQL
Reply with quote

Hi All,

My problem is not new or anything exciting. All I have to do is to udpate a bunch of columns based on a particular value from a parent column.

I tried the following (syntatically proved wrong icon_exclaim.gif )

UPDATE EMPLOYEE_TABLE
SET LAST_MODIFIED_DTM = CURRENT TIMESTAMP,
IF DEPT_NAME = '101'
THEN
SET DESIGNATION = 'MANAGER'
SET BASE_SALARY = 70000
SET EMPLOYEE_CODE = 'A01'
ELSEIF DEPT_NAME IS NULL
THEN
SET DESIGNATION = 'CONTRACTOR'
SET BASE_SALARY = 50000
SET EMPLOYEE_CODE = 'A0C'
ENDIF
WHERE ORGANIZATION_NAME = 'XYZ'

I tried the CASE LOGIC but then its proving to be more statements per column.

Could anyone please let me know if you ever tried a IF-ELSE logic in the UPDATE sql?

I am trying to achieve it programtically but just wanted to check in this forum before I go ahead with the traditional approach.

My apologies if I asked a nomadic question. Also, please let me know if you need more details and I will be more than happy to share.

Thanks a many
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Feb 25, 2014 9:49 pm    Post subject:
Reply with quote

Cant you have two SQL's

with

Code:
WHERE DEPT_NAME = '101'

and

Code:
WHERE DEPT_NAME IS NULL
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Feb 25, 2014 10:34 pm    Post subject:
Reply with quote

I would do as Pandora suggested,
for no other reason than your single sql will

SET LAST_MODIFIED_DTM = CURRENT TIMESTAMP

for all of ORGANIZATION_NAME = 'XYZ'
regardless of DEPT_NAME.
Back to top
View user's profile Send private message
ravindra.vadali

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Tue Feb 25, 2014 10:53 pm    Post subject: Reply to: IF-ELSE/CASE logic in a UPDATE SQL
Reply with quote

Thank you both for your suggestions!!
I kind of did the same in my source code, but I wanted to check if there could be a way to achieve if via an single UPDATE sql.

Once again I appreciate your time and patience to look into my issue.

Regards,
Ravi
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Feb 26, 2014 2:38 pm    Post subject:
Reply with quote

It is possible, if you take out IF-ELSE part outside of UPDATE query and use a simple cobol host variables and move the corresponding values to them as per DEPT_NAME and then perform a UPDATE query.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Wed Feb 26, 2014 2:49 pm    Post subject:
Reply with quote

Rohit,

For doing IF-ELSE with host variable, he has to SELECT first.

I guess, Mr. Ravi wouldn't agree with you icon_smile.gif .. as it is too mainstream icon_lol.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Feb 26, 2014 2:51 pm    Post subject:
Reply with quote

Suresh,

As he is already doing a hard coding of those values everywhere , so I personally don't think he needs any select. i hope so... icon_biggrin.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Feb 27, 2014 1:52 am    Post subject:
Reply with quote

I think TS is more concerned on performance of the query and hence tryint out different options.

Pandora,

I see what you suggested would solve the purpose but it looks like DEPT_NAME is a nullable column and we don't know the volume of data in the table and hence this could cause a performance issues.

Correct me if I misunderstood.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Feb 27, 2014 11:50 am    Post subject:
Reply with quote

Rohit,

Talking of performance issues
1.If the field DEPT_NAME is not Indexed it could cause performance issues.
2.If there are too many records to be updated there is a high possibility that query to fail with -904 and then in that case I would have a cursor to select and update the data with frequent commits.

Thats how I would approach it.
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 -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 Wildcard logic in COBOL dudenithy COBOL Programming 13 Thu Sep 01, 2016 7:36 pm
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