Hi All,

Please can someone assist with the following problem.

Scenario: I need to return the Sales Line of a Particular Sales Agent.
The Table that Gets Queried:
CREATE TABLE M_SELLINGENTITY
(
INTRECNO INTEGER NOT NULL,
REPORTINTO VARCHAR( 15) DEFAULT '' COLLATE NONE,
SELLINGCODE VARCHAR( 15) COLLATE NONE,
EXTERNALCODE VARCHAR( 20) DEFAULT '' COLLATE NONE,
SELLNAMES VARCHAR( 50) DEFAULT '' COLLATE NONE,
SELLSURNAME VARCHAR( 50) DEFAULT '' COLLATE NONE,
....SNIP....
CONSTRAINT PK_M_SELLINGENTITY PRIMARY KEY (INTRECNO)
);

Explanation: The REPORTINGINTO field contains the value of the
SELLINGCODE into wehich the Sales Agent reports. If the Sales Agent
does not report to anyone, the value 'NIL' is stored in the
REPORTINGINTO field.

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;
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?

Firebird Version Used : 1.5.3


Regards
Marius J. Labuschagne

Turnkey Information Systems cc
Cell: 083 578 2702
Fax: 043 748 6885
Tel: 043 748 6424

Skype: marius_j_labuschagne

Email: <mailto:mjl@...>MJ Labuschagne
Web Address: http://www.turnkey-is.co.za



This message may contain information which is confidential, private
or privileged in nature and subject to legal privilege. If you are
not the intended recipient, you may not peruse, use, disseminate,
distribute or copy this message or file which is attached to this
message. Opinions, conclusions and other information in this message
that do not relate to the official business of our firm shall be
understood as neither given nor endorsed by it. If you have received
this message in error, please notify the sender immediately by
e-mail, facsimile or telephone and thereafter return and/or destroy
the original message.

Please note that the recipient must scan this e-mail and any attached
files for viruses and the like. Turnkey Information Systems cc
accepts no liability of whatever nature for any loss, liability,
damage or expense resulting directly or indirectly from the access of
any files which are attached to this message.
----------



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


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