Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: SQL0104N when using temporary table

    I am trying to write a db2 stored procedure in the development center. (Please see the script below.) The follow message is returned in the output view:

    SAMPLEDB.TEST1 - Build started.Create stored procedure returns -104. SAMPLEDB.TEST1: 15: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "SELECT SLSPID FROM SESSION.T1 ORDER BY" was found following "= ARCUS.CODESLSP1; ". Expected tokens may include: "<call>". LINE NUMBER=15. SQLSTATE=42601

    SAMPLEDB.TEST1 - Build failed.
    SAMPLEDB.TEST1 - Roll back completed successfully.


    Any advice would be most appreciated.

    Thanks,
    Chanonna

    ==============================================

    CREATE PROCEDURE SAMPLEDB.TEST1 ( )

    P1: BEGIN

    DECLARE GLOBAL TEMPORARY TABLE T1
    (SLPSPID CHAR(8), CUSTID CHAR(12))
    ON COMMIT PRESERVE ROWS NOT LOGGED;

    INSERT INTO SESSION.T1

    SELECT ARSAP.CODESLSP, ARCUS.IDCUST
    FROM SAMPLEDB.ARSAP AS ARSAP
    LEFT JOIN SAMPLEDB.ARCUS AS ARCUS ON ARSAP.CODESLSP = ARCUS.CODESLSP1;

    SELECT SLSPID FROM SESSION.T1 ORDER BY SLSPID;

    COMMIT;

    SELECT count(CUSTID) from SESSION.T1;

    CONNECT RESET;

    END P1

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Chanonna,

    Try it this way:

    CREATE PROCEDURE SAMPLEDB.TEST1 ( )

    P1: BEGIN

    DECLARE GLOBAL TEMPORARY TABLE T1
    (SLPSPID CHAR(8), CUSTID CHAR(12))
    ON COMMIT PRESERVE ROWS NOT LOGGED;

    INSERT INTO SESSION.T1

    SELECT ARSAP.CODESLSP, ARCUS.IDCUST
    FROM SAMPLEDB.ARSAP AS ARSAP
    LEFT JOIN SAMPLEDB.ARCUS AS ARCUS ON ARSAP.CODESLSP = ARCUS.CODESLSP1;
    COMMIT;

    BEGIN
    DECLARE CURSOR1 CURSOR for
    SELECT SLSPID FROM SESSION.T1 ORDER BY SLSPID;

    DECLARE cursor2 cursor for
    SELECT count(CUSTID) from SESSION.T1;

    OPEN cursor1;
    OPEN CURSOR2;
    END;
    END P1


    select statements, just by themselves are meaningless (and thus not allowed) in a stored procedure. DB2 needs to know what you want to do with the result set. That is why a cursor is needed.

    HTH

    Andy

  3. #3
    Join Date
    Dec 2003
    Posts
    8
    Hi Andy:

    Thank you for your reply. I tried deploying the script you suggested, and it returned the following error message:


    SAMPLEDB.TEST1 - Build started.
    Create stored procedure returns -206.
    SAMPLEDB.TEST1: 16: [IBM][CLI Driver][DB2/NT] SQL0206N "SLSPID" is not valid in the context where it is used. LINE NUMBER=16. SQLSTATE=42703

    SAMPLEDB.TEST1 - Build failed.
    SAMPLEDB.TEST1 - Roll back completed successfully.

    ===========================

    LINE 16 refers to the first DECLARE statement.

    Any other thoughts?

    Chanonna

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by ChanOnna
    SAMPLEDB.TEST1: 16: [IBM][CLI Driver][DB2/NT] SQL0206N "SLSPID" is not valid in the context where it is used. LINE NUMBER=16. SQLSTATE=42703

    You may want to check spelling of the temp table column names in the cursor declaration...

  5. #5
    Join Date
    Dec 2003
    Posts
    8
    Thank you for the prompt reply.

    I was quite excited there for a moment.

    Unfortunately, even with the spelling corrected, the error is still the same.

    Is db2 particular with parentheses?
    I remember something about "()" when searching for answers to the error message.


    -- Chanonna

  6. #6
    Join Date
    Dec 2003
    Posts
    8
    Hi all:

    Very sorry to have taken up your time...

    Just wanted to let you know that I had spelled it wrong the second time too, and it worked after correcting the spelling.

    Thanks again!

    Best,
    Chanonna

Posting Permissions

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