From: Date: July 30 2003 2:34pm
Subject: nested while statements / nested loops
List-Archive: http://lists.mysql.com/maxdb/17151
Message-Id: <3F27BB5D.7030006@pharmakologie.uni-erlangen.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
maybe someone can help me with my problem.
I'm trying to chain more than one while-statement, but it won't work.

Thank you for your help.
Andi
----------------------------------------------
Create a result table:
CREATE TABLE TABLE3
(
    "A"               Char (11) ASCII,
    "B"               Char (11) ASCII,
    "C"               Char (1) ASCII,
)
------------------------------------------------

This one works (only one while-statement):
CREATE DBPROC Test
AS
VAR var1 char(11); var2 char(11); var3 CHAR(1);
set var1 ='0'; set var2 ='0'; set var3= '0';
TRY
    SELECT VAR1, VAR2 FROM TABLE1;
    WHILE $rc = 0 DO BEGIN
          FETCH INTO :var1, :var2;
   
      insert into TABLE3 (a,b,c) Values (:var1, :var2, :var3);
    END;
CATCH
IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

---------------------------------------------------

This one won't work, only one entry is generated !!!!
CREATE DBPROC Test
AS
VAR var1 char(11); var2 char(11); var3 CHAR(1);
set var1 ='0'; set var2 ='0'; set var3= '0';
TRY
    SELECT VAR1, VAR2 FROM TABLE1;
    WHILE $rc = 0 DO BEGIN
          FETCH INTO :var1, :var2;
      SELECT VAR3 FROM TABLE2 WHERE VAR1 = :var1;
        WHILE $rc = 0 DO BEGIN
               FETCH INTO :var3;
          insert into TABLE3 (a,b,c) Values (:var1, :var2, :var3);
        END;
    END;
CATCH
IF $rc <> 100 THEN STOP ($rc, 'unexpected error');


-- 


Andreas Ackermann
Institute for Experimental and Clinical Pharmacology and Toxicology,
Friedrich-Alexander University of Erlangen
Fahrstrasse 17
D-91054 Erlangen

Tel. +-49-9131-8522-968
Fax  +-49-9131-8522-236
e-mail: ackermann@stripped