IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

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


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Calling an Open C library function in... CICS 1
No new posts DATE2 function SYNCSORT 15
No new posts Help on PL/I jsonPutValue function PL/I & Assembler 8
No new posts Insert header record with record coun... DFSORT/ICETOOL 14
No new posts Insert system time/date (timestamp) u... DFSORT/ICETOOL 5
Search our Forums:

Back to Top