× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I am trying to create a SQL stored procedure. I need to open 2 cursors for
selects from 2 different tables (I cannot join these tables together to get
the results I want). I have included an example of what I am trying to do
(yes, I know it doesn't do anything but the actual code is about 350 lines).

When I try to run this statement I get the error:
SQL0199  30      21  Position 11 Keyword DECLARE not expected. Valid tokens:
                     ( IF GET SET CALL CASE DROP GOTO LOCK OPEN WITH ALTER
                     CLOSE.
for the line where the second DECLARE CURSOR statement is.

I have tried declaring both cursors at the beginning of the procedure; but
that has not
worked either. It does work if I take out all the code referencing the
second cursor.
I am using V5R1.

The only documentation I can find for SQL Procedures is in  this book:
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/sqlp/rbafymst02.htm#T
oC_204
(DB2 UDB for iSeries SQL Programming Concepts V5R1)
but it is not very thorough.

Here is the example:
CREATE PROCEDURE jehimes/Testproc ()
        LANGUAGE SQL READS SQL DATA
         BEGIN
          DECLARE ID            INT;
          DECLARE ENDTABLE      INT DEFAULT 0;
          DECLARE BAD           INT DEFAULT 0;

          DECLARE C1 CURSOR FOR
          select c1nmid from lbucmfil/nammsp where c1nmid<999 ;
          DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET ENDTABLE = 1;
          DECLARE EXIT HANDLER FOR SQLEXCEPTION
            SET BAD=1;

          OPEN C1;
            FETCH C1 INTO ID  ;
            WHILE ENDTABLE = 0 DO
             FETCH C1 INTO ID  ;
            END WHILE;
          CLOSE C1;
          SET  ENDTABLE =0;
          SET  BAD       =0;

          DECLARE C2            CURSOR FOR
          select c3nmid from lbucmfil/biomsp where c3nmid<999;
          DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET ENDTABLE = 1;
          DECLARE EXIT HANDLER FOR SQLEXCEPTION
            SET BAD=1;

          OPEN C2;
            FETCH C2 INTO ID  ;
            WHILE ENDTABLE = 0 DO
              FETCH C2 INTO ID  ;
             END WHILE;
           CLOSE C2;
           SET  ENDTABLE =0;
           SET  BAD       =0;

          END ;

Any help (manuals; suggestions; examples) would be greatly appreciated.




As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.