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
 
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 How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm

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