Hi All,

Thanks for the help.

I actually found the solution. I had a look at previous posts (about
October 2005) and found something that Martijn stated, I used what he
suggested over there and found the following to work like a
charm. I'm sure someone else with this hierarchical type setup will
be able to use it, so here's the DDL for the procedure, same table
structures apply (the most important to remember is that the
M_SELLINGENTITY table has a recursive relationship with itself <1 to 0:1:M> )

CREATE PROCEDURE P_CONSTRUCT_DOWNLINE (
TOPLEVEL VarChar(15))
returns (
REPORTINTO VarChar(15),
SELLINGCODE VarChar(15),
SELLNAME VarChar(50),
SELLSURNAME VarChar(50))
AS
/*
Procedure: P_CONSTRUCT_DOWNLINE

Author : MJ Labuschagne, Turnkey Information Systems cc
Date : 12/06/2006 07:05:15 AM
Modified Date : 13/06/2006 07:24:00 AM
Purpose : To Reconstruct the Downline given a Selling Code
*/
declare variable THISLEVEL varchar(15);
begin
for select ReportInto, SellingCode, SellNames, SellSurname
from M_SELLINGENTITY
where REPORTINTO = :TOPLEVEL
into :REPORTINTO, :SELLINGCODE, :SELLNAME, :SELLSURNAME do
begin
IF (SELLINGCODE IS NOT NULL) THEN
BEGIN
SUSPEND;
THISLEVEL = SELLINGCODE;
FOR SELECT ReportInto, SellingCode, SellName,
SellSurname FROM P_CONSTRUCT_DOWNLINE (:THISLEVEL)
INTO :REPORTINTO, :SELLINGCODE, :SELLNAME, :SELLSURNAME
DO
SUSPEND;
END
end
end

Regards
Marius Labuschagne

At 12/06/2006 12:19 PM, you wrote:

>Marius,
>
>
> >The Stored Procedure so far:
> >SET TERM ^^ ;
> >CREATE PROCEDURE P_CONSTRUCT_DOWNLINE
> >(TOPLEVEL VarChar(15)) returns (REPORTINTO VarChar(15), SELLINGCODE
>VarChar(15), SELLNAME VarChar(50), SELLSURNAME VarChar(50))
>AS
>/*
> Procedure: P_CONSTRUCT_DOWNLINE
>
> Author : MJ Labuschagne, Turnkey Information Systems cc
> Date : 12/06/2006 07:05:15 AM
> Purpose : To Reconstruct the Downline given a Selling Code
> Params
> ------
> THISLEVEL : <purpose>
>*/
>declare variable THISLEVEL varchar(15);
>begin
> for select ReportInto, SellingCode, SellNames, SellSurname
> from M_SELLINGENTITY
> where REPORTINTO = :TOPLEVEL
> into :REPORTINTO, :SELLINGCODE, :SELLNAME, :SELLSURNAME do
> begin
> SUSPEND;
> THISLEVEL = :SELLINGCODE;
> >
> >
>
>Use "select ... from P_CONSTRUCT_DOWNLINE(:THISLEVEL)" in
>order to use SUSPEND properly. If you don't, only 1 line will be returned.
>
>
> > execute procedure P_CONSTRUCT_DOWNLINE (:THISLEVEL)
> >RETURNING_VALUES :REPORTINTO, :SELLINGCODE, :SELLNAME, :SELLSURNAME;
> > SUSPEND;
> > end
> >end
> >^^
> >SET TERM ; ^^
> >
> >The Problem:
> >
> >The output that gets produced is correct, with the exception that
> >from level 3 no data is returned. I have stepped trough the
> >procedure using Database Workbench and can actually see the values
> >which are not being returned, so I think that there is something
> >wrong with my suspends?
>
>Martijn Tonies
>Database Workbench - development tool for Firebird and more!
>Upscene Productions
>http://www.upscene.com
>My thoughts:
>http://blog.upscene.com/martijn/
>Database development questions? Check the forum!
>http://www.databasedevelopmentforum.com

----------



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.8.3/362 - Release Date: 12/06/2006


[Non-text portions of this message have been removed]