I believe the problem in both cases is that the value $rc is reset by EVERY access to the database.
In the first case below, you can not get out of the inner loop until the $rc is some value other than 0, at which point it no longer matches the criteria for continuing the outer loop. It is guaranteed that you will only execute the outer loop one time.
In the second case the outcome is not so clear, however when the logical test to continue the outer loop occurs, the value of $rc will not be the result set by the FETCH INTO, it will be that set by either the return from the call, or from the SET instruction (I do not know if SET affects the $rc or not) In any event, it is not the value you want to test.
In both cases you would need to store the result of the SELECT or FETCH instruction into a temporary variable if you wish to use it for loop control.
Regards,
John Neal
-----Original Message-----
From: Michael Comanns [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 04, 2003 6:37 AM
To: [EMAIL PROTECTED]
Subject: nested while statements / nested loops
> maybe someone can help me with my problem.
> I'm trying to chain more than one while-statement, but it won't work.
[...]
> 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;
If you want to iterate through more than one cursor at a time, you'll have
to name them. See DECLARE CURSOR on how to name them and FETCH
<result_table_name> on how to access them.
Daniel Dittmar
--
Daniel Dittmar
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
Hi,
I've got a similar problem but I did not really understand the answer.
I want to iterate through 2 loops, and here is what I've tried, but it
didn't work.
CREATE DBPROC FILLDOKUTIEFE(IN DUMMY INT)
AS
VAR FACode INT ; MONAT INT ;
SET MONAT = 1;
DECLARE test CURSOR FOR SELECT "FA-Code" FROM OWN.Abteilungen;
WHILE $RC = 0 DO BEGIN
FETCH test INTO :FACode;
WHILE MONAT <= 12 DO BEGIN
CALL OWN.FILLDOKUTABLE(:FACODE, :MONAT);
SET MONAT = MONAT + 1;
END;
END;
The parameter for my inner loop is just a simple integer but the outer loop
is only executed once. Maybe someone can help me.
Thanks
--------------------------------------------------------------------------------------------------
Michael Comanns
cand.-Inform.
synaix
Beratung f�r das Gesundheitswesen GmbH
Im Suesterfeld 1
52072 Aachen, Deutschland
Amtsgericht Aachen HRB8137, GF Stefan Fritz, Dr. Kurt Becker
Mailto:[EMAIL PROTECTED]
http://www.synaix-healthcare.de
--------------------------------------------------------------------------------------------------
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
