Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: Plz help to rectify this error

    While compling this procedure i am getting the following error
    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 "END LOOP" was found following " END IF;
    ". Expected tokens may include: "<psm_statement>".


    CREATE PROCEDURE SSOGDF.MEAN_COMPARE(IN P_MAXIMOPOOLID VARCHAR(25)
    ,IN TO_WEEK INTEGER
    ,IN FRM_YEAR INTEGER,IN TO_YEAR INTEGER
    ,IN V_SLID VARCHAR(5),IN V_SLCID VARCHAR(5)
    ,IN P_INDI_COL VARCHAR(50),P_MEAN INTEGER,P_BM INTEGER,P_AM INTEGER)

    BEGIN

    DECLARE V_MAXIMO_POOL_ID VARCHAR(25);
    DECLARE V_SERVICELINEID VARCHAR(5);
    DECLARE V_SLCOMPID VARCHAR(5);
    DECLARE V_WEEKS INTEGER;
    DECLARE V_YEAR INTEGER;
    DECLARE BELOW_MEAN INTEGER DEFAULT 0;
    DECLARE ABOVE_MEAN INTEGER DEFAULT 0;
    DECLARE V_INDI_VALUE INTERGER;
    DECLARE V_MEAN INTEGER;
    DECLARE V_UNPL DOUBLE;
    DECLARE V_LNPL DOUBLE;
    DECLARE V_MR_MEAN INTEGER;
    DECLARE V_TOT_TICKET INTEGER;
    DECLARE V_MR INTEGER;
    DECLARE V_UNPL_MR DOUBLE;
    DECLARE V_TODATE TIMESTAMP;
    DECLARE V_FRMDATE TIMESTAMP;
    DECLARE FRM_WEEK INTEGER;
    DECLARE V_STMT VARCHAR(500);
    DECLARE V_SQL VARCHAR(500);
    DECLARE at_end INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE TKT_CUR1 CURSOR FOR V_STMT ;

    SET BELOW_MEAN = P_BM;
    SET ABOVE_MEAN = P_AM;
    SET V_MEAN = P_MEAN;
    SET FRM_WEEK = TO_WEEK - 5;

    IF (FRM_WEEK <= 0) AND (FRM_YEAR <> TO_YEAR) THEN
    SET FRM_WEEK = 52 + FRM_WEEK;
    SET TO_YEAR = FRM_YEAR;
    ELSE IF (FRM_WEEK <= 0) AND (FRM_YEAR = TO_YEAR) THEN
    SET FRM_WEEK = 1;

    END IF;

    BEGIN
    SELECT MIN(FULLDATEALTERNATEKEY)
    INTO V_FRMDATE
    FROM SSOGDF.TIME_DIM
    WHERE CALENDARWEEKNO = FRM_WEEK AND CALENDARYEAR = FRM_YEAR;

    SELECT MAX(FULLDATEALTERNATEKEY)
    INTO V_TODATE
    FROM SSOGDF.TIME_DIM
    WHERE CALENDARWEEKNO = TO_WEEK AND CALENDARYEAR = TO_YEAR;
    END;

    DECLARE GLOBAL TEMPORARY TABLE SESSION.WEEKTKT_TMP(
    MAXIMO_POOL_ID VARCHAR(25)
    ,YEAR INTEGER
    ,WEEK INTEGER
    ,TOT_TICKET INTEGER
    )
    WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

    INSERT INTO SESSION.WEEKTKT_TMP
    SELECT MAXIMO_POOL_ID ,B.CALENDARYEAR
    ,B.CALENDARWEEKNO,COUNT(P_INDI_COL)
    FROM SSOGDF.TICKET_FCT A,SSOGDF.TIME_DIM B,SSOGDF.GDC_INFO C
    WHERE UPPER(MAXIMO_POOL_ID) = UPPER(P_MAXIMO_POOLID)
    AND A.PK_TIMEKEY = B.PK_TIMEKEY
    AND B.FULLDATEALTERNATEKEY BETWEEN V_FRMDATE AND V_TODATE
    AND A.GDCINFO_ID = C.GDCINFO_ID
    AND C.SERVICELINEID = V_SLID
    AND C.SLCOMPID = V_SLCID
    GROUP BY MAXIMO_POOL_ID,B.CALENDARYEAR,B.CALENDARWEEKNO;

    SET V_SQL = 'SELECT MAXIMO_POOL_ID,WEEK,YEAR,TOT_TICKET FROM SESSION.WEEKTKT_TMP';
    PREPARE V_STMT FROM V_SQL;
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

    BEGIN
    OPEN TKT_CUR1;
    in_loop :
    LOOP
    FETCH TKT_CUR INTO V_MAXIMO_POOL_ID,v_WEEKS,V_YEAR,V_TICKET_MR;
    IF at_end = 1 then
    leave in_loop;
    ELSE IF (V_TOT_TICKET < V_MEAN) THEN
    SET BELOW_MEAN = BELOW_MEAN + 1;
    ELSE IF (V_TOT_TICKET > V_MEAN) THEN
    SET ABOVE_MEAN = ABOVE_MEAN + 1;
    ELSE
    SET BELOW_MEAN = 0;
    SET ABOVE_MEAN = 0;
    END IF;
    END LOOP;

    IF (BELOW_MEAN = 6) THEN
    INSERT INTO SSOGDF.EXCEPTION_INFO(MAXIMO_POOL_ID,WEEK,YEAR,MET RICS,STATUS,EXCEPTION_ID)
    VALUES (V_MAXIMO_POOL_ID,V_WEEKS,V_YEAR,P_INDI_COL,1,'6') ;

    INSERT INTO SSOGDF.MEASLES_RPT (WEEK,YEAR,MAXIMO_POOL_ID,REPORT_TYPE,FLAG,EXCEPTI ON_ID)
    VALUES(V_WEEKS,V_YEAR,V_MAXIMO_POOL_ID,P_INDI_COL, 1,'6');

    ELSE IF (ABOVE_MEAN = 6) THEN
    INSERT INTO SSOGDF.EXCEPTION_INFO(MAXIMO_POOL_ID,WEEK,YEAR,MET RICS,STATUS,EXCEPTION_ID)
    VALUES (V_MAXIMO_POOL_ID,V_WEEKS,V_YEAR,P_INDI_COL,1,'5') ;

    INSERT INTO SSOGDF.MEASLES_RPT (WEEK,YEAR,MAXIMO_POOL_ID,REPORT_TYPE,FLAG,EXCEPTI ON_ID)
    VALUES(V_WEEKS,V_YEAR,V_MAXIMO_POOL_ID,P_INDI_COL, 1,'5');
    END IF;
    CLOSE TKT_CUR1;
    END;
    END @

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Perhaps,
    replace
    "DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; "
    to the next to BEGIN.

  3. #3
    Join Date
    Dec 2009
    Posts
    15
    thanks for u'r response. i replaced that statement still i am getting the error like .

    During SQL processing it returned:
    SQL0104N An unexpected token "END LOOP" was found following "MEAN = 0; END
    IF; ". Expected tokens may include: "<psm_statement>". LINE NUMBER=99.
    SQLSTATE=42601

    SQL0104N An unexpected token "END LOOP" was found following "MEAN = 0;
    END IF;
    ". Expected tokens may include: "<psm_statement> ".

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about to change all "ELSE IF" to "ELSEIF"?
    I saw at least four "ELSE IF".

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    check the name of your cursor:

    DECLARE TKT_CUR1 CURSOR FOR V_STMT

    FETCH TKT_CUR INTO V_MAXIMO_POOL_ID,v_WEEKS,V_YEAR,V_TICKET_MR;

  6. #6
    Join Date
    Dec 2009
    Posts
    15
    i have changed the all else if to "elseif " now it's working fine. thanks a lot for u'r help

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I suggest that you start with an empty procedure body and then add 1 statement after the other and try to create and run/call the procedure after each step.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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