Insert Clause using sqltalk

[shirleyc]

How do you insert to a field that is next number? such as piece number.

[FRBhote]

INSERT INTO Table ( field ) SELECT MAX( field ) + 1 FROM Table;

[Mike Vandine]

What version of SQLBase? Are you looking to use the Autoincrement column type?

[shirleyc]

sqlbase 11.5.3

[shirleyc]

This is my current Script. Where would I add that select Max? under Values.

INSERT INTO requirement (workorder_type,workorder_base_id,workorder_lot_id,workorder_split_id,workorder_sub_id,operation_seq_no,part_id,status,qty_per,qty_per_type,peice_no)
VALUES ( :1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
\
M, RX-485-4-S , 0 , 0 , 0 , 40 , 70-30-0003 , U , 3 , S
M , RX-485-2-13 , 0 , 0 , 0 , 40 , 70-30-0003 , U , 3 , S
M, RX-485-2-S , 0 , 0 , 0 , 40 , 70-30-0003 , U , 3 , S
M, MX-485-4-13 , 0 , 0 , 0 , 40 , 70-30-0003 , U , 3 , S
M, MX-485-4-S , 0 , 0 , 0 , 40 , 70-30-0003 , U , 3 , S
/;

[Mike Vandine]

Hi Shirley,

Since you are on 11.5, you should look into having the column defined as an 'Auto-increment'. You then don't have to worry about inserting *anything* into the column. When you do a standard insert, the next number gets created automagically.

Have a look at the Language Guide, SQL Commands, Create Table.

[shirleyc]

Hi Mike,
Thanks for the info but a little beyond my understanding,just an end user trying to avoid a lot of manual labor. I believe the piece_no is auto incremented but if I leave that field out during insert is gives me the error: 00331 EXE NEV Not enough Non-null values.
If I add the field and give it a value my script runs okay. My work around has been adding a value greater than any assigned previously. This is not the best solution as it adds a piece_no out of sequence.

Can you give me an Idea using the my script below where to add the SELECT MAX( field ) + 1 FROM Table you suggested earlier
INSERT INTO requirement (workorder_type,workorder_base_id,workorder_lot_id,workorder_split_id,
workorder_sub_id,operation_seq_no,part_id,status,qty_per,qty_per_type,calc_qty,piece_no)
VALUES ( :1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12)
\
M , MRR-8202 , 0 , 0 , 0 , 50 , 70-30-0003 , U , 4 , S , 4.00,???

[Jeff Luther]

Hi - let me jump in, maybe I can help. I just ran this test in SQLTalk for SQLBase 11.6:

create table incrTest( id integer not null auto_increment(1,1), vc varchar( 10 ) );
insert into incrTest( vc) values( 'one' );

and see I get this inserted:

Code: Select all

    ID         VC
=========== ==========
     1         one

Thus, it looks like you should not include piece_no in your INSERT( <columns> ) list.

[shirleyc]

Seems that way, but when I don't add the piece_no column to the insert I get the "error: 00331 EXE NEV Not enough Non-null values".

[Mike Vandine]

Hi Shirley,

Can you show us a definition of the table columns? You can do that by doing an 'unload schema c:\schema.sql;', then texting in that schema.sql file and scanning for a 'create table sysadm.requirements', then copy that table definition.

You need to make sure that the column definition on the piece column is 'integer not null auto_increment'.