Re: beginner procedure problem

"Peter Möller" wrote: > > I managed to create a procedure that does the work expected > and it returns the data I want. The problem is that I can't > access the data. When I run the procedure (in dbaccess) I get this: > > (expression) 10 > (expression) 11 > (expression) GENERALTULL > (expression) 8802 > (expression) 180.0000000000 > (expression) 180.0000000000 > (expression) X > > This is what I want but I'd like to assign names to the data > and be able to insert it into a temp table. > > EXECUTE PROCEDURE foreach_test() INTO TEMP foo;> > or somthing like this. > > Pointers to resources and faq also wanted. > > Thanks in adwance... > Peter > > The procedure is below. > > drop procedure foreach_test; > create procedure foreach_test() > returning int foo, int, char(15), int, float, float, char(1);> define site, branch, volgnr int; > define p_vstnr_filnaam, p_filnr_filnaam, p_akti_nummer int; > define p_bedrag_verkoop, p_bedrag_inkoop float; > define p_zoek_supplzd char(15); > define p_x_bedrag char; > > foreach > select vstnr_filnaam, filnr_filnaam, volg_nr volgnr > into site, branch, volgnr > from zendalg > where vstnr_filnaam = 10 > and filnr_filnaam = 11 > and afdnr_afdtabel = 2 > and shipment_date between '990101' and '991231' > foreach > select vstnr_filnaam ,filnr_filnaam, zoek_supplzd, > akti_nummer, bedrag_verkoop, bedrag_inkoop, > x_bedrag > into p_vstnr_filnaam ,p_filnr_filnaam, p_zoek_supplzd, > p_akti_nummer, p_bedrag_verkoop, p_bedrag_inkoop, > p_x_bedrag > from supplzdr > where vstnr_filnaam = site > and filnr_filnaam = branch > and vlgnr_zendalg = volgnr > return p_vstnr_filnaam ,p_filnr_filnaam, p_zoek_supplzd, > p_akti_nummer, p_bedrag_verkoop, p_bedrag_inkoop, > p_x_bedrag with resume;> > end foreach > > end foreach > end procedure ; Just do a CREATE TEMP TABLE before calling the procedure. -- ******************************************************************************* * Any opinions written in this e-mail are my own. They may not correspond to * * those of my company. * ******************************************************************************* * Wolfgang Zager Berghauser Str. 104 * * BAB Data-Systems GmbH D-42349 Wuppertal * * Abt. Programmierung Tel. : +49 202 479870 * * Fax : +49 202 470035 * * E-Mail: zager@gmx.de * *******************************************************************************

Wolfgang Zager <zager@gmx.de> writes: > "Peter M'ller" wrote: > > > > I managed to create a procedure that does the work expected > > and it returns the data I want. The problem is that I can't > > access the data. When I run the procedure (in dbaccess) I get this: > > > > (expression) 10 > > (expression) 11 > > (expression) GENERALTULL > > (expression) 8802 > > (expression) 180.0000000000 > > (expression) 180.0000000000 > > (expression) X > > > > This is what I want but I'd like to assign names to the data > > and be able to insert it into a temp table. > > > > EXECUTE PROCEDURE foreach_test() INTO TEMP foo;> > > > or somthing like this. > > > > Pointers to resources and faq also wanted. > > > > Thanks in adwance... > > Peter > > > > The procedure is below. > > <procedure snipped> > > end foreach > > end procedure ; > > Just do a CREATE TEMP TABLE before calling the procedure. > -- I made a temp table before calling the procedure and then insert into that in the procedure. It works but I'm not very happy with this solution. The procedure needs to know the name of the temp table and the temp table needs to know the datatypes and output of the procedure. -- /Peter http://www.badtech.com http://www.ctoons.com http://sluggy.com http://www.unitedmedia.com http://www.userfriendly.org http://www.slagoon.com http://www.kingfeatures.com/comics http://www.mg.co.za/mg/m&e/today.htm

"Peter Möller" wrote: > > Wolfgang Zager <zager@gmx.de> writes: > > > "Peter Möller" wrote: > > > > > > I managed to create a procedure that does the work expected > > > and it returns the data I want. The problem is that I can't > > > access the data. When I run the procedure (in dbaccess) I get this: > > > > > > (expression) 10 > > > (expression) 11 > > > (expression) GENERALTULL > > > (expression) 8802 > > > (expression) 180.0000000000 > > > (expression) 180.0000000000 > > > (expression) X > > > > > > This is what I want but I'd like to assign names to the data > > > and be able to insert it into a temp table. > > > > > > EXECUTE PROCEDURE foreach_test() INTO TEMP foo;> > > > > > or somthing like this. > > > > > > Pointers to resources and faq also wanted. > > > > > > Thanks in adwance... > > > Peter > > > > > > The procedure is below. > > > > > <procedure snipped> > > > > end foreach > > > end procedure ; > > > > Just do a CREATE TEMP TABLE before calling the procedure. > > -- > > I made a temp table before calling the procedure > and then insert into that in the procedure. It works > but I'm not very happy with this solution. > The procedure needs to know the name of the temp table > and the temp table needs to know the datatypes and output > of the procedure. > > -- > /Peter > http://www.badtech.com http://www.ctoons.com http://sluggy.com > http://www.unitedmedia.com http://www.userfriendly.org http://www.slagoon.com > http://www.kingfeatures.com/comics http://www.mg.co.za/mg/m&e/today.htm Instead of a CREATE TEMP TABLE You can do a SELECT field1, field2.... FROM orgininal_table WHERE 1 = 2before executing Your procdure, so You get a table-design excactly the way You want. -- ******************************************************************************* * Any opinions written in this e-mail are my own. They may not correspond to * * those of my company. * ******************************************************************************* * Wolfgang Zager Berghauser Str. 104 * * BAB Data-Systems GmbH D-42349 Wuppertal * * Abt. Programmierung Tel. : +49 202 479870 * * Fax : +49 202 470035 * * E-Mail: zager@gmx.de * *******************************************************************************