Re: nested while statements / nested loops

[Andreas Ackermann]

Hello again, in my opinion the problem is that a procedure is stoped the first time the END; command occurs. See this little example with only one while-loop and a simple insert-statement following: CREATE DBPROC TEST AS VAR var1 INTEGER; var2 CHAR(100) SELECT VAR1, VAR2 FROM TABLE1; WHILE $rc = 0 DO BEGIN FETCH INTO :var1, :var2; INSERT INTO TABLE2 (a,b) Values (:var1, :var2); END; INSERT INTO TABLE2 (a,b) Values (99, 'this insert statement will never happen!!!'); If you call the procedure the while loop will be correctly executed. But you will not get a result from the second insert statement. Seems to me that the END; command terminates the hole procedure. Therefore it is not possible to nest more than one loop, or to chain one after another. Please correct my if i'm wrong (and i hope so). Regards Andreas Zabach, Elke schrieb: Michael Comanns wrote: 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 You should check the value of $RC after calling OWN.FILLDOKUTABLE(:FACODE, :MONAT); for at least :MONAT = 12 I assume it returns something <> 0, the inner loop ends because :monat > 12 and the outer one ends because $RC <> 0 (received not by the FETCH-statement, but by some other statement). Or you should use a local variable, store $RC of the fetch in it and ask for that local variable in the outer loop. BTW: do you think it is a good idea to do the inner loop, even if :FACODE is not set, because the fetch returned with some error, for example error 100 after the last row?! Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general -- 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: [EMAIL PROTECTED]