Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    16

    Unanswered: Need help with Stored Procedure

    I am trying to set up the below stored procedure and keep getting the error in the bottom. Please help.

    CREATE PROCEDURE WFRS.UPP_PI (IN tmp_prd_dt DATE)
    SPECIFIC SP_UPI
    BEGIN
    declare v_stmt VARCHAR(32672);
    set v_stmt='DECLARE MA CURSOR WITH RETURN FOR SELECT DISC_PROC_DT, DATE(PROC_MO_END_DT) AS PROC_MO_END_DT, SUM(DISC_AMT) AS DISC_AMT FROM
    (SELECT DISC_PROC_DT, DATE(SUBSTR(CHAR(PROC_DT+1 MONTH,ISO),1,7) || '-01')-1 DAY PROC_MO_END_DT,DISC_AMT
    FROM ICAD.REMBMT_DISC_CLM DM WHERE DISC_PROC_DT =tmp_prd_dt) MA GROUP BY DISC_PROC_DT, PROC_MO_END_DT
    ORDER BY 1' ;

    SET v_load_stmt =
    'LOAD FROM MA OF CURSOR METHOD N(DISC_PROC_DT, PROC_MO_END_DT, DISC_AMT )
    INSERT INTO WFRS.UPP_PROD_STAGG (DISC_PROC_DT, PROC_MO_END_DT, DISC_AMT )';

    CALL sysproc.db2load ( v_version_number, v_cur,v_load_stmt,
    v_sqlcode, v_sqlmessage, v_rows_read, v_rows_skipped,
    v_rows_loaded, v_rows_rejected, v_rows_deleted, v_rows_committed,
    v_rows_part_read, v_rows_part_rejected,
    v_rows_part_partitioned, v_mpp_load_summary) ;

    END
    @


    Error :

    SQL0104N An unexpected token "')-1 DAY PROC_MO_END_DT,DISC_AMT FROM IC" was
    found following "TH,ISO),1,7) || '-01". Expected tokens may include:
    "<space>". LINE NUMBER=5. SQLSTATE=42601

  2. #2
    Join Date
    Mar 2009
    Posts
    16

    Need help with Stored Procedure

    The SQL runs fine without any issues when run from DB2 Control center client

    Database environment : DB2 Windows v9.5

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    set v_stmt='DECLARE MA CURSOR .....TH,ISO),1,7) || '-01')-1 DAY PROC_MO_END_DT,DISC_AMT
    FROM IC..... ORDER BY 1' ;

  4. #4
    Join Date
    Mar 2009
    Posts
    16
    Not sure from the response what needs to change in the SQL.

    My SQL that gives me error within stored procedure is:

    set v_stmt='DECLARE MA CURSOR WITH RETURN FOR SELECT DISC_PROC_DT, DATE(PROC_MO_END_DT) AS PROC_MO_END_DT, SUM(DISC_AMT) AS DISC_AMT FROM
    (SELECT DISC_PROC_DT, DATE(SUBSTR(CHAR(PROC_DT+1 MONTH,ISO),1,7) || '-01')-1 DAY PROC_MO_END_DT,DISC_AMT
    FROM ICAD.REMBMT_DISC_CLM DM WHERE DISC_PROC_DT =tmp_prd_dt) MA GROUP BY DISC_PROC_DT, PROC_MO_END_DT
    ORDER BY 1' ;

    In the response also the SQL looks the same as my current SQL...?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sujith_g
    Not sure from the response what needs to change in the SQL.
    Imagine that you are the DB2 SQL compiler and try to figure out where is the end of the string that starts with "DECLARE MA CURSOR...".
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2009
    Posts
    16

    Need Help!!! Stored Procedure to Load - DB2 Windows v9.5

    I was able to fix the select SQL to work by fixing the "STRING". I am still having issues with creating the stored procedure. Any help on what I might be missing appreciated.

    CREATE PROCEDURE WFRS.UPP_PI1 (IN tmp_prd_dt DATE)
    SPECIFIC SP_UPI1
    BEGIN
    -- Load variable declaration
    DECLARE v_txt VARCHAR(10000);
    DECLARE v_stmt STATEMENT;
    --declare v_cur VARCHAR(32672);
    declare v_load_stmt VARCHAR(32672);

    --set v_cur='DECLARE MA CURSOR WITH RETURN FOR v_stmt';

    set v_txt=
    'SELECT UPP_PROV_ID, UPP_PROD_ID, UPP_MO_ID, DISC_PROC_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT
    FROM
    (SELECT UPP_PROV_ID, UPP_MO_ID, DISC_PROC_DT, MA.PROV_NBR, REMBMT_PROD_TYP_CD, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT FROM
    (SELECT UPP_MO_ID, DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT FROM
    (SELECT DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, PRD_MO_END_DT, DATE(PROC_MO_END_DT) AS PROC_MO_END_DT,
    DATE(HOSP_FISC_YR_END_DT) AS HOSP_FISC_YR_END_DT,SUM(DISC_AMT) AS DISC_AMT FROM
    (SELECT DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, (DISC_PROC_DT+ 1 MONTH-1 DAY) AS PRD_MO_END_DT,
    DATE(SUBSTR(CHAR(PROC_DT+1 MONTH, ISO),1,7)' || '|| ' || '''' || '-01' || '''' || ')-1 DAY AS PROC_MO_END_DT,
    DATE(SUBSTR(CHAR(PROC_DT, ISO),1,4)' || '|| ' || '''' || '-12-31' || '''' || ') AS HOSP_FISC_YR_END_DT,DISC_AMT
    FROM ICAD.REMBMT_DISC_CLM DM WHERE DISC_PROC_DT =' || '''' || RTRIM(CHAR(tmp_prd_dt)) || '''' || ') MA
    GROUP BY DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, PRD_MO_END_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT
    ORDER BY 1) MA LEFT OUTER JOIN UPP.UPP_MO M
    ON MA.PRD_MO_END_DT=M.MO_END_DT) MA LEFT OUTER JOIN UPP.UPP_PROV P
    ON MA.PROV_NBR=P.PROV_NBR) MA LEFT OUTER JOIN UPP.UPP_PROD P
    ON MA.REMBMT_PROD_TYP_CD=P.UPP_PROD_ID_DESC';

    prepare v_stmt from v_txt;

    set v_load_stmt =
    'LOAD FROM MA OF CURSOR INSERT INTO WFRS.UPP_PROD_STAGG (UPP_PROV_ID, UPP_PROD_ID, UPP_MO_ID, DISC_PROC_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT)';

    CALL sysproc.db2load (1, 'DECLARE MA CURSOR WITH RETURN FOR v_stmt',
    v_load_stmt,?, '', ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL);

    END

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "<variable declaration>" was found following "".
    Expected tokens may include: "<SQL statement>". LINE NUMBER=8.
    SQLSTATE=42601

    SQL0104N An unexpected token "<variable declaration>" was found following "". Expected tokens may include: "<SQL statement> ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be processed.

    User response:

    Examine and correct the statement in the area of the specified token.

    sqlcode: -104

    sqlstate: 42601
    Last edited by sujith_g; 03-31-09 at 14:55. Reason: Title change

  7. #7
    Join Date
    Mar 2009
    Posts
    16

    Smile Solution

    I got the stored procedure working . Here is the solution..incase anyone else using DB2 windows 9.5 ever run into a need. works like a charm inserting huge volume of data in seconds..

    CREATE PROCEDURE WFRS.UPP_PI2 (IN tmp_prd_dt DATE)
    SPECIFIC SP_UPI2
    BEGIN

    DECLARE v_txt VARCHAR(10000);

    set v_txt=
    'SELECT UPP_PROV_ID, UPP_PROD_ID, UPP_MO_ID, DISC_PROC_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT
    FROM
    (SELECT UPP_PROV_ID, UPP_MO_ID, DISC_PROC_DT, MA.PROV_NBR, REMBMT_PROD_TYP_CD, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT FROM
    (SELECT UPP_MO_ID, DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT FROM
    (SELECT DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, PRD_MO_END_DT, DATE(PROC_MO_END_DT) AS PROC_MO_END_DT,
    DATE(HOSP_FISC_YR_END_DT) AS HOSP_FISC_YR_END_DT,SUM(DISC_AMT) AS DISC_AMT FROM
    (SELECT DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, (DISC_PROC_DT+ 1 MONTH-1 DAY) AS PRD_MO_END_DT,
    DATE(SUBSTR(CHAR(PROC_DT+1 MONTH, ISO),1,7)' || '|| ' || '''' || '-01' || '''' || ')-1 DAY AS PROC_MO_END_DT,
    DATE(SUBSTR(CHAR(PROC_DT, ISO),1,4)' || '|| ' || '''' || '-12-31' || '''' || ') AS HOSP_FISC_YR_END_DT,DISC_AMT
    FROM ICAD.REMBMT_DISC_CLM DM WHERE DISC_PROC_DT =' || '''' || RTRIM(CHAR(tmp_prd_dt)) || '''' || ') MA
    GROUP BY DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, PRD_MO_END_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT
    ORDER BY 1) MA LEFT OUTER JOIN UPP.UPP_MO M
    ON MA.PRD_MO_END_DT=M.MO_END_DT) MA LEFT OUTER JOIN UPP.UPP_PROV P
    ON MA.PROV_NBR=P.PROV_NBR) MA LEFT OUTER JOIN UPP.UPP_PROD P
    ON MA.REMBMT_PROD_TYP_CD=P.UPP_PROD_ID_DESC';

    CALL SYSPROC.ADMIN_CMD('LOAD FROM (DATABASE ICADD ' || v_txt || ') OF CURSOR INSERT INTO WFRS.UPP_PROD_STAGG
    (UPP_PROV_ID, UPP_PROD_ID, UPP_MO_ID, DISC_PROC_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT)');

    END
    @

Posting Permissions

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