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
 
Get max in insert is not working because of the subquey

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

New User


Joined: 20 Feb 2006
Posts: 34

PostPosted: Wed Feb 07, 2007 6:16 pm    Post subject: Get max in insert is not working because of the subquey
Reply with quote

Hi,

I have a table T1 with following fields
SEQ NUM,
NAME

SEQ NUM is primary key of the table.

I have some records in T1 with name M1.

I want to write
insert into T1
(SEQ NUM,
NAME)
SELECT
(select max(SEQ NUM)+1 from T1),
M2)

This query is not working because of the subquery which i have used to increase the SEQ NUM.
Could any one suggest how to do this insert?
any ideas plz

Regards
Rashmi
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Feb 07, 2007 9:52 pm    Post subject: Re: get max in insert
Reply with quote

Your looking to insert a single record with this SQL execution? (Not multiple records incrementing seq_num)?
Back to top
View user's profile Send private message
rashmi.ranjan75

New User


Joined: 20 Feb 2006
Posts: 34

PostPosted: Wed Feb 07, 2007 10:33 pm    Post subject:
Reply with quote

there are mulitple records
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Feb 08, 2007 6:20 am    Post subject: Re: Get max in insert is not working because of the subquey
Reply with quote

rashmi.ranjan75 wrote:
SELECT
(select max(SEQ NUM)+1 from T1),
M2)



So what is the error you are getting? It would be helpful to paste it here.

To me it seems that there is no FROM clause in your select. May be you can add one "from sysibm.sysdummy1".
Back to top
View user's profile Send private message
rashmi.ranjan75

New User


Joined: 20 Feb 2006
Posts: 34

PostPosted: Thu Feb 08, 2007 1:53 pm    Post subject:
Reply with quote

sql error code coming is -4700 and sqlstate is 56038

the actual query is

insert into T1
(SEQ NUM,
NAME)
SELECT
(select max(SEQ NUM)+1 from T1),
M2)
from
T1;

Thanks to see if it is possible somehow
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Feb 08, 2007 6:42 pm    Post subject:
Reply with quote

Try this query,This should work

insert into T1 (SEQ NUM, NAME)
SELECT max(SEQ NUM)+1,'M2' from T1 ;
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 REXX LISTDSI () SMSINFO is not working upendrasri CLIST & REXX 4 Fri Nov 17, 2017 11:33 am
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 LIBDEF not working in called proc packerm CLIST & REXX 0 Fri Sep 15, 2017 7:22 pm
No new posts Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am

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