INSERT INTO query with MAX() possible ?

[saustin]

Hi,
Have a table that I need to INSERT INTO. One field (TRANSACTION_ID)needs to be unique and incremented so this
sql works perfectly to come up with that new value.

SELECT (Max(SYSADM_LABOR_TICKET.TRANSACTION_ID) +1) AS expmax FROM SYSADM_LABOR_TICKET;

The problem is that you get a reserved error (-3025)when trying to put this into an insert query.

INSERT INTO SYSADM_LABOR_TICKET (TRANSACTION_ID)
SELECT (SELECT(Max(SYSADM_LABOR_TICKET.TRANSACTION_ID) +1) AS expmax FROM SYSADM_LABOR_TICKET

Is there a work-around for this ? Many thanks, Steve.

 

[jrbarnett]

This should do it:

INSERT INTO SYSADM_LABOR_TICKET (TRANSACTION_ID)
SELECT Max(SYSADM_LABOR_TICKET.TRANSACTION_ID)+1 AS expmax FROM SYSADM_LABOR_TICKET;

John

 

[saustin]

Many thanks for the reply John !

With one field this works perfectly but just to complicate things needed to add several other fields to this table and wanted to verify the syntax as am now getting the "number of query values and destination fields are not the same" error message.

Think the key is still my misunderstanding of the max subquery syntax.

Here is the sql.

INSERT INTO SYSADM_LABOR_TICKET (TRANSACTION_ID, EARNING_CODE_ID, EMPLOYEE_ID, DEPARTMENT_ID, HOURLY_COST, UNIT_COST, HOURS_WORKED, ACT_LABOR_COST, MULTIPLIER_1, MULTIPLIER_2, TRANSACTION_DATE, USER_ID, GL_ACCT_ID, BURDEN_PER_HR, BURDEN_PER_UNIT, BURDEN_PERCENT, BUR_PER_OPERATION, POSTING_CANDIDATE, INDIRECT_CODE, INDIRECT_ID, SHIFT_DATE, HOURS_BREAK_IND, ACT_BURDEN_COST, CREATE_DATE, WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, RESOURCE_ID, OPERATION_SEQ_NO, TYPE, SETUP_COMPLETED )
SELECT (SELECT Max(SYSADM_LABOR_TICKET.TRANSACTION_ID)+1 AS expmax FROM SYSADM_LABOR_TICKET AS EXPR1, "REGUL" AS EXPR2, "010012" AS EXPR3, "RECEPTION" AS EXPR4, 15.25 AS EXPR6, 0 AS EXPR7, 8 AS EXPR8, 122 AS EXPR9, 1 AS EXPR10, 1 AS EXPR11, #8/31/2003# AS EXPR12, "SYSADM" AS EXPR13, "7501-001" AS EXPR14, 0 AS EXPR15, 0 AS EXPR16, 0 AS EXPR17, 0 AS EXPR18, "Y" AS EXPR19, "V" AS EXPR20, "VAC 01" AS EXPR21, #8/31/2003# AS EXPR22, 0 AS Expr23, 0 AS Expr24, Now() AS Expr25, "W" AS Expr27, "" AS Expr28, "" AS Expr29, "" AS Expr30, "" AS Expr31, "" AS Expr5, "" AS Expr26, "I" AS Expr32, "N" AS Expr33;

 

[jrbarnett]

Hi,

There are two types of insert query:

Insert into tablename (field1, field2, field3) values (value1, value2, value3) will add one row to the table with the data specified.

Insert into tablename (field1, field2, field3) select field1, field2, field3 from table2 will copy the data from table2 to tablename as many records are in there (you can use a where clause to restrict them further).

From your SQL I think that you are using the wrong type of insert query. Try the following:

INSERT INTO SYSADM_LABOR_TICKET (TRANSACTION_ID, EARNING_CODE_ID, EMPLOYEE_ID, DEPARTMENT_ID, HOURLY_COST, UNIT_COST, HOURS_WORKED, ACT_LABOR_COST, MULTIPLIER_1, MULTIPLIER_2, TRANSACTION_DATE, USER_ID, GL_ACCT_ID, BURDEN_PER_HR, BURDEN_PER_UNIT, BURDEN_PERCENT, BUR_PER_OPERATION, POSTING_CANDIDATE, INDIRECT_CODE, INDIRECT_ID, SHIFT_DATE, HOURS_BREAK_IND, ACT_BURDEN_COST, CREATE_DATE, WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, RESOURCE_ID, OPERATION_SEQ_NO, TYPE, SETUP_COMPLETED) 
VALUES (SELECT Max(TRANSACTION_ID)+1 FROM SYSADM_LABOR_TICKET), "REGUL", "010012", "RECEPTION", 15.25, 0, 8, 122, 1, 1, #8/31/2003#, "SYSADM", "7501-001", 0, 0, 0, 0, "Y", "V", "VAC 01", #8/31/2003#, 0, 0, Now(), "W", "", "", "", "", "", "", "I", "N";

In either case, the number of fields in the top list must match the number in the values or select statement.

John

 

[saustin]

Hi John,

Many thanks for the reply. The query type is now much clearer to me. Have made the changes but am still getting the syntax error in query expression on VALUES(SELECT Max(TRANSACTION_ID)+1 FROM SYSADM_LABOR_TICKET),

The error is on the SELECT. Have tried alternates but with the same resulting error (i.e.).

VALUES (SELECT (Max(SYSADM_LABOR_TICKET.TRANSACTION_ID)+1) FROM SYSADM_LABOR_TICKET,

Steve.

 

[jrbarnett]

Picture Homer Simpsoh going D'Oh and bashing his head against a wall. The Values statement should be enclosed in brackets - but on my original only the first value is.
Try this instead:

INSERT INTO SYSADM_LABOR_TICKET (TRANSACTION_ID, EARNING_CODE_ID, EMPLOYEE_ID, DEPARTMENT_ID, HOURLY_COST, UNIT_COST, HOURS_WORKED, ACT_LABOR_COST, MULTIPLIER_1, MULTIPLIER_2, TRANSACTION_DATE, USER_ID, GL_ACCT_ID, BURDEN_PER_HR, BURDEN_PER_UNIT, BURDEN_PERCENT, BUR_PER_OPERATION, POSTING_CANDIDATE, INDIRECT_CODE, INDIRECT_ID, SHIFT_DATE, HOURS_BREAK_IND, ACT_BURDEN_COST, CREATE_DATE, WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, RESOURCE_ID, OPERATION_SEQ_NO, TYPE, SETUP_COMPLETED)
VALUES ((SELECT Max(TRANSACTION_ID)+1 FROM SYSADM_LABOR_TICKET), "REGUL", "010012", "RECEPTION", 15.25, 0, 8, 122, 1, 1, #8/31/2003#, "SYSADM", "7501-001", 0, 0, 0, 0, "Y", "V", "VAC 01", #8/31/2003#, 0, 0, Now(), "W", "", "", "", "", "", "", "I", "N"

John

 

[saustin]

Thanks John !
Love the Homer Simpson ! Picture me doing that now as I get an "RESERVED ERROR (-3025)" error when running the sql. Am researching this now. Quite a pain.
Can get around this by running two queries, the first a select max.... and then just passing that value to the second insert query but perhaps not kill too much time finding a solution. Steve.