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: 1535
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: 6967
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: 1720
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: 1720
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: 1720
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: 1535
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 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
No new posts Need help in building a logic Benchwarmer All Other Mainframe Topics 4 Wed Feb 22, 2017 2:49 am
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm


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