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
 

 

How to use max function in INSERT INTO statement DB2 V8 Z\OS

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

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Wed Sep 23, 2009 5:03 pm    Post subject: How to use max function in INSERT INTO statement DB2 V8 Z\OS
Reply with quote

Table A having Ref_nbr as integer,col1,col2...

I need to calculate dynamically the max integer value in insert statement, should accomplish it in interactive mode using BATCH util DSNTEP2

INSERT into table A
Select Max(ref_nbr)+1 from table A,col1,col2,.. hard coded values
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Sep 23, 2009 5:32 pm    Post subject:
Reply with quote

Jagadesh,

Can you please provide more details.

Just in case, you can try the following.

Code:
INSERT into tableA
Select Max(ref_nbr)+1,'Hard','Coded','Values'
from tableA;
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Wed Sep 23, 2009 9:52 pm    Post subject: Reply to: How to use max function in INSERT INTO statement D
Reply with quote

A table having primary key as a single integer column,
Table A
Ref_nbr col1 col2 col3
1
2
3
4
5
now I need to insert one row by adding 1 to max(ref_nbr) value.
I tried this query, its working.
INSERT into table A
Sel B.Ref,'Hard','Code' from
(sel max(Ref_nbr) +1 as Ref from table A) B
Thanks for your concern Srihari!
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Sep 24, 2009 3:07 am    Post subject:
Reply with quote

The given solution is fine (and has been programmed/implemented in previous versions of DB2) , but if implemented in an operational system this could become a locking bottleneck.

You know DB2 has something like identifiers and/or sequences ?
This lets DB2 handle the automatic incrementing of a column/value. google about and see if this meets your requirement.
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 ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 1 Tue Jul 25, 2017 1:20 pm
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am


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