Results 1 to 3 of 3

Thread: sql procedure

  1. #1
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Unanswered: sql procedure

    hI PEOPLE,

    Somebody could help me with this error that i receive?

    Thanks


    SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=35. SQLSTATE=42601

    Take a look:

    CREATE PROCEDURE RMLEITE.update_sls ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN



    declare v_CO_ID SMALLINT ;
    declare v_STR_ID SMALLINT ;
    declare v_YR_WK_ID INTEGER;
    declare v_SKU_ID INTEGER ;
    declare v_TS_VAL DECIMAL(11,2);
    declare V_TS_QTY INTEGER;
    declare V_ROS_SLS_QTY INTEGER DEFAULT 0;
    declare V_NUM_SKU_STRS INTEGER DEFAULT 0;
    declare V_TPOS_MD_VAL DECIMAL(11,2);
    declare V_CLR_MD_VAL DECIMAL(11,2);
    declare V_TS_VAL_EUR DECIMAL(11,2);
    declare V_TPOS_MD_VAL_EUR DECIMAL(11,2);
    declare V_CLR_MD_VAL_EUR DECIMAL(11,2);
    declare V_BASE_CCY CHAR(3);
    DECLARE v_cont INTEGER DEFAULT 0;
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';


    -- Exception Handlers
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    declare insere CURSOR WITH HOLD FOR
    SELECT
    SL.CO_ID ,
    SL.STR_ID ,
    SL.YR_WK_ID ,
    SL.SKU_ID ,
    SL.TS_VAL ,
    SL.TS_QTY ,
    SL.ROS_SLS_QTY ,
    SL.NUM_SKU_STRS ,
    SL.TPOS_MD_VAL ,
    SL.CLR_MD_VAL ,
    SL.TS_VAL_EUR ,
    SL.TPOS_MD_VAL_EUR,
    SL.CLR_MD_VAL_EUR,
    SL.BASE_CCY

    FROM BASEDETT.SLS SL
    WHERE SL.STR_ID=75 AND SL.YR_WK_ID=200128
    FETCH FIRST 1000 ROWS ONLY
    FOR READ ONLY;

    OPEN insere;
    FETCH insere INTO
    v_CO_ID ,
    v_STR_ID ,
    v_YR_WK_ID ,
    v_SKU_ID ,
    v_TS_VAL ,
    v_TS_QTY ,
    v_ROS_SLS_QTY ,
    v_NUM_SKU_STRS ,
    v_TPOS_MD_VAL ,
    v_CLR_MD_VAL ,
    v_TS_VAL_EUR ,
    v_TPOS_MD_VAL_EUR,
    v_CLR_MD_VAL_EUR,
    v_BASE_CCY;


    WHILE at_end = 0 DO
    INSERT INTO basedett.sls_tmp
    values(
    v_CO_ID ,
    v_STR_ID ,
    v_YR_WK_ID ,
    v_SKU_ID ,
    v_TS_VAL ,
    v_TS_QTY ,
    v_ROS_SLS_QTY ,
    v_NUM_SKU_STRS ,
    v_TPOS_MD_VAL ,
    v_CLR_MD_VAL ,
    v_TS_VAL_EUR ,
    v_TPOS_MD_VAL_EUR,
    v_CLR_MD_VAL_EUR,
    v_BASE_CCY );
    SET v_cont = v_cont + 1;
    IF v_cont > 500 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;

    FETCH insere INTO v_CO_ID ,
    v_STR_ID ,
    v_YR_WK_ID ,
    v_SKU_ID ,
    v_TS_VAL ,
    v_TS_QTY ,
    v_ROS_SLS_QTY ,
    v_NUM_SKU_STRS ,
    v_TPOS_MD_VAL ,
    v_CLR_MD_VAL ,
    v_TS_VAL_EUR ,
    v_TPOS_MD_VAL_EUR,
    v_CLR_MD_VAL_EUR,
    v_BASE_CCY;
    END WHILE;
    close insere;
    COMMIT;
    END P1

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    You must declare your cursors before your handlers!

  3. #3
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74
    Thanks for your help
    Originally posted by Damian Ibbotson
    You must declare your cursors before your handlers!

Posting Permissions

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