Hi

I am kinda new at this...I have the Following Stored Procedure

*****************
CREATE PROCEDURE NROSWI.AMEND_EVENT ( P_EVENT_ID INT, P_MODE INT, P_REASON VARCHAR(254), P_TERMINATION_DATE DATE, P_USER_ID INT)
SPECIFIC NROSWI.AMEND_EVENT
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE CONSTANT_AMEND_CODE_ID INT;
DECLARE CONSTANT_CANCEL_CODE_ID INT;
DECLARE NEW_EVENT_ID INT;

SET NEW_EVENT_ID = NEXT VALUE FOR NROSWI.EVENT_SEQ;
SET CONSTANT_AMEND_CODE_ID = 54;
SET CONSTANT_CANCEL_CODE_ID = 56;


FOR L1 AS SELECT
NRCSWAP_APPLICATION_ID, NRCSWCT_EVENT_CODE_ID, NRCSWEV_NO_SEQUENCE,
NRCSWDP_DIST_PRACT_ID, NRCSWCT_LIFESPAN_CODE_ID,
NRCSWCT_INSTALLED_UNIT_CODE_ID,
NRCSWMX_MAXIMUM_ID, NRCSWAG_ALLOCATION_GROUP_ID,
NRCSWCT_RECONSTRUCTION_CODE_ID
...
FROM NROSWI.NRTSWI_EVENT
WHERE NRCSWEV_EVENT_ID = P_EVENT_ID DO

INSERT INTO
NROSWI.NRTSWI_EVENT (NRCSWEV_EVENT_ID, NRCSWAP_APPLICATION_ID,
NRCSWCT_EVENT_CODE_ID, NRCSWEV_NO_SEQUENCE,
NRCSWDP_DIST_PRACT_ID, NRCSWCT_LIFESPAN_CODE_ID,
NRCSWMX_MAXIMUM_ID, NRCSWAG_ALLOCATION_GROUP_ID, ...)
VALUES (NEW_EVENT_ID, L1.NRCSWAP_APPLICATION_ID,
CASE P_MODE WHEN 0 THEN CONSTANT_AMEND_CODE_ID ELSE
CONSTANT_CANCEL_CODE_ID END,
L1.NRCSWEV_NO_SEQUENCE + 1,
L1.NRCSWDP_DIST_PRACT_ID, L1.NRCSWCT_LIFESPAN_CODE_ID,
L1.NRCSWMX_MAXIMUM_ID, L1.NRCSWAG_ALLOCATION_GROUP_ID, ....);
END FOR;
*****************

Problem is I need to get the maximum number from the NRCSWEV_NO_SEQUENCE and then +1 with it when i am inserting instead of L1.NRCSWEV_NO_SEQUENCE..

I tried to solve it like this..
========
FOR L54 AS SELECT TEST AS MAX(NRCSWEV_NO_SEQUENCE)
FROM NROSWI.NRTSWI_EVENT
WHERE NRCSWEV_EVENT_ID = P_EVENT_ID DO
END FOR;

========
I Inserted my code before L1 and
and instead of L1.NRCSWEV_NO_SEQUENCE i am using L54.TEST
but its giving me the following error

NROSWI.AMEND_EVENT: 15: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "(" was found following "4 AS SELECT TEST MAX". Expected tokens may include: ",". LINE NUMBER=15. SQLSTATE=42601

Can any one Help please

never mind

got the solution
just in case somebody else have the same problem here its is how you solve it

DECLARE MAX_SEQUENCE INT;

SET NEW_EVENT_ID = NEXT VALUE FOR NROSWI.EVENT_SEQ;
SET CONSTANT_AMEND_CODE_ID = 54;
SET CONSTANT_CANCEL_CODE_ID = 56;

SELECT MAX(NRCSWEV_NO_SEQUENCE) into MAX_SEQUENCE
FROM NROSWI.NRTSWI_EVENT
WHERE NRCSWEV_EVENT_ID = P_EVENT_ID;

Then use MAX_SEQUENCE where u need it to use