Is there much documentation on DBPROC? I have looked, but not found much. This thread to which I am replying contains the most extensive code examples that I have been able to find.
Also, is there an in-process programming language available for SAPDB, similar in purpose and scope to Oracle's PL-SQL or their internal Java (http://www.orafaq.com/faqplsql.htm#WHAT)? Mike > 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] _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
