Re: Auto-increment record counter on INSERT

How exactly are you running this? You might do the SELECT prior to the insert and just use a variable to hold the MAX(t_srno)+1 value. Otherwise you can try INSERT INTO tltcom200500 (i_item, t_pror, t_cmnt, t_srno) SELECT "Desc", 2, "Comment", (MAX(t_srno)+1) FROMtltcom200500; I have no idea if that'll work, but it's probably the first thing I'd try off the top of my head. Can I ask why you're inserting directly into a Baan table and not using the interface? I'm not sure sure what the tltcom200 table does, but you risk RI problems by poking the database directly... > We have a system that I'd like to update with a single INSERT statement > that has a 'key' (not a real index but unique) that I want to > auto-increment. I've done the following but I get a syntax error on the > select max() part: > > INSERT INTO tltcom200500 (t_item, t_pror, t_cmnt, t_srno) > VALUES ( "Desc", 2, "Comment", select max(t_srno) + 1 from> tltcom200500); > > I know the select max() part works because I did it by itself and it > returned the value I wanted. Can anyone tell me what I'm doing wrong, > or a better way to do it? > > Thanks! > > Sam > >

We want to limit the number of Baan licenses we're using, so I'm doing this thru a web interface. I'm kinda new at it, so it's pretty much trial and error at this point. The table is a custom table we made (hence the lt package). We kept the naming conventions so that it would get backed up thru Baan's nightly dump of the tables into flat files. We do that as well as a DB backup, since it's easier to restore using baan. Since the tables not part of the Baan package, I think we're pretty safe. Chuck Renaud wrote: > How exactly are you running this? You might do the SELECT prior > to the insert and just use a variable to hold the MAX(t_srno)+1 value. > > Otherwise you can try > > INSERT INTO tltcom200500 (i_item, t_pror, t_cmnt, t_srno) > SELECT "Desc", 2, "Comment", (MAX(t_srno)+1) FROM> tltcom200500; > > I have no idea if that'll work, but it's probably the first thing I'd try off > the top of my head. > > Can I ask why you're inserting directly into a Baan table and not > using the interface? I'm not sure sure what the tltcom200 table > does, but you risk RI problems by poking the database directly... > > > We have a system that I'd like to update with a single INSERT statement > > that has a 'key' (not a real index but unique) that I want to > > auto-increment. I've done the following but I get a syntax error on the > > select max() part: > > > > INSERT INTO tltcom200500 (t_item, t_pror, t_cmnt, t_srno) > > VALUES ( "Desc", 2, "Comment", select max(t_srno) + 1 from> > tltcom200500); > > > > I know the select max() part works because I did it by itself and it > > returned the value I wanted. Can anyone tell me what I'm doing wrong, > > or a better way to do it? > > > > Thanks! > > > > Sam > > > >