Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: Need help with Stored Procedure

    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

  2. #2
    Join Date
    May 2006
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •