Hi,

You don't need a recursive or re-entrant query:

Here is a stored procedure which does exactly what you are doing but no
recursive call.

SET TERM ^ ;
ALTER PROCEDURE P_DOWNLINEOFUSER (
USERIDIN Varchar(10) )
RETURNS (
PARENTID Varchar(10),
USERID Varchar(10),
DOWNLINE Smallint )
AS
BEGIN
for
with recursive temptable as
( select userid, 0 as der_level, USERIDKEY,useridsparent from USERIDS
where userid = :USERIDIN
union all
select userid, temptable.der_level+1, USERIDKEY, useridsparent from
userids inner join temptable on userids.useridsparent= temptable.USERIDKEY)
select userid, der_level, useridsparent from temptable into :userid,
:DOWNLINE, :PARENTID
do
suspend;
END^
SET TERM ; ^


GRANT EXECUTE
ON PROCEDURE P_DOWNLINEOFUSER TO SYSDBA;

Regards

Z T

Lee Jenkins wrote:

> Hi all,
>
> I've read about this somewhere (maybe Helen's book) and I think Re-entrant query
> is what I need.
>
> I have a table:
>
> Tourneys
> OID: varchar
> ParentOID: varchar
>
> A Tourney can have "sub" tournaments (each could be part of a series tournament
> or even satellite tournament into a larger one) and each of those tournaments
> can have sub tournaments, etc.
>
> 1. Would anyone care to provide a simple example or pseudo code showing the
> basic logic of such queries? For instance, off the top of my head I would
> probably need a query that tested if a particular tournament's OID was already
> present in it's parent's tournaments hirearchy, etc so that I don't add a
> tournament farther from root using a tournament that was created closer to root.
>
> 2. What about performance with queries like these? I imagine that a recursive
> Store Procedure could be hard put to handle very complex trees but I can't
> imagine going more than 5 levels deep at any node but I guess that could be a
> lot as well if some nodes hold many peer nodes.
>
>
> Thanks for guidance,
>
> --
> Warm Regards,
>
> Lee
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
>
>