Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: DB2 SP syntax problem.

    I have been porting MSSQL 2k stored procs to DB2 with much success. However, I'm receiving an error on this query:

    SELECT DISTINCT CompetencyID, CompetencyName, isComplete, isMgrComplete FROM SESSION."#t1";

    The error im receiving is:

    DB2INST1.SP_DCS_GETCOMPETENCIESFORPOSITION: 265: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "SESSION" was found following ", isMgrComplete FROM". Expected tokens may include: "INTO". LINE NUMBER=265. SQLSTATE=42601

    What am I missing?




    The entire stored procedure is listed below:

    CREATE PROCEDURE sp_dcs_getCompetenciesForPosition(
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------

    IN v_mPositionID INT,
    IN v_mUniqAssessmentIDD INT)
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    DECLARE SQLCODE INT;
    DECLARE l_sqlcode INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
    SET l_sqlcode = SQLCODE;



    DECLARE GLOBAL TEMPORARY TABLE SESSION."#t1" AS
    (SELECT DISTINCT CompetencyID, CompetencyName, PositionID,
    0 AS isComplete, 0 AS isMgrComplete
    FROM vwSkillCompetencyMap
    ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;


    DECLARE GLOBAL TEMPORARY TABLE SESSION."#t4" AS
    (SELECT COUNT(*) AS SKILLCOUNT, mtmpositioncompskill.CompetencyID
    FROM SESSION."#t1" INNER JOIN mtmpositioncompskill ON mtmpositioncompskill.PositionID
    =
    SESSION."#t1".PositionID
    AND
    mtmpositioncompskill.CompetencyID
    =
    SESSION."#t1".CompetencyID INNER JOIN tblAssessment ON
    tblAssessment.PositionCompSkillID = mtmpositioncompskill.PositionCompSkill
    GROUP BY mtmpositioncompskill.CompetencyID
    ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;


    DECLARE GLOBAL TEMPORARY TABLE SESSION."#t5" AS
    (SELECT COUNT(*) AS SKILLCOUNT, mtmpositioncompskill.CompetencyID
    FROM SESSION."#t1" INNER JOIN mtmpositioncompskill ON mtmpositioncompskill.PositionID
    =
    SESSION."#t1".PositionID
    AND
    mtmpositioncompskill.CompetencyID
    =
    SESSION."#t1".CompetencyID INNER JOIN tblAssessment ON
    tblAssessment.PositionCompSkillID = mtmpositioncompskill.PositionCompSkill
    GROUP BY mtmpositioncompskill.CompetencyID
    ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
    BEGIN


    DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
    SELECT DISTINCT CompetencyID, CompetencyName, isComplete, isMgrComplete
    FROM SESSION."#t1"
    ORDER BY CompetencyName ;



    DECLARE temp_cursor1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
    SELECT SESSION."#t1".*
    FROM SESSION."#t1";


    INSERT INTO SESSION."#t1"
    SELECT DISTINCT CompetencyID, CompetencyName, PositionID,
    0 AS isComplete, 0 AS isMgrComplete
    FROM vwSkillCompetencyMap
    WHERE PositionID = v_mPositionID;


    IF v_mUniqAssessmentIDD <> -1 THEN



    INSERT INTO SESSION."#t4"
    SELECT COUNT(*) AS SKILLCOUNT, mtmpositioncompskill.CompetencyID
    FROM SESSION."#t1" INNER JOIN mtmpositioncompskill ON mtmpositioncompskill.PositionID
    =
    SESSION.#t1.PositionID
    AND
    mtmpositioncompskill.CompetencyID
    =
    SESSION.#t1.CompetencyID INNER JOIN tblAssessment ON
    tblAssessment.PositionCompSkillID =
    mtmpositioncompskill.PositionCompSkill
    WHERE UserScore = -1 AND
    tblAssessment.UniqUserAssessmentIDD = v_mUniqAssessmentIDD
    GROUP BY mtmpositioncompskill.CompetencyID;


    INSERT INTO SESSION."#t5"
    SELECT COUNT(*) AS SKILLCOUNT, mtmpositioncompskill.CompetencyID
    FROM SESSION."#t1" INNER JOIN mtmpositioncompskill ON mtmpositioncompskill.PositionID
    =
    SESSION.#t1.PositionID
    AND
    mtmpositioncompskill.CompetencyID
    =
    SESSION.#t1.CompetencyID INNER JOIN tblAssessment ON
    tblAssessment.PositionCompSkillID =
    mtmpositioncompskill.PositionCompSkill
    WHERE MgrScore = -1 AND
    tblAssessment.UniqUserAssessmentIDD = v_mUniqAssessmentIDD
    GROUP BY mtmpositioncompskill.CompetencyID;


    UPDATE SESSION."#t1"
    SET isComplete = 1
    WHERE CompetencyID NOT IN (SELECT CompetencyID
    FROM SESSION."#t4");
    COMMIT;



    UPDATE SESSION."#t1"
    SET isMgrComplete = 1
    WHERE CompetencyID NOT IN (SELECT CompetencyID
    FROM SESSION."#t5");
    COMMIT;



    SELECT DISTINCT CompetencyID, CompetencyName, isComplete, isMgrComplete
    FROM SESSION."#t1";
    OPEN temp_Cursor1
    ELSE
    SELECT *
    FROM SESSION."#t1";
    END IF;
    END;
    END

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    First off, I believe you will have to create cursors for all your intended SELECT statements.

    Secondly, if you are to declare cursors based on the contents of temporary session tables, you must nest the declarations inside another BEGIN-END block.

    Also, I don't believe your temporary tables will contain any data. There might be a way to include the data using one of the copy options, although I haven't found one. I've always resorted to doing an explicit INSERT.

    e.g.

    CREATE SP_TMP (IN VAR VARCHAR(10))
    LANGUAGE SQL

    BEGIN

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_TABLE
    ...

    BEGIN

    DECLARE C_TMP CURSOR
    FOR SELECT * FROM SESSION.TMP_TABLE;

    INSERT INTO SESSION.TMP_TABLE
    ...

    OPEN C_TMP;

    END;

    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
  •