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

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

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

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

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

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