Re: Adjacency list to Nested set model statement

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 4 Mar 2002 13:56:53 -0800
Message-ID: <c0d87ec0.0203041356.6f2454a9_at_posting.google.com>


>> I've cleaned up the code and posted it at:

http://willets.org/treecrawl.sql <<

I just picked up a copy and it looks great! You are right about the whole approach and my stuff stinks.

PL/SQL is very close to the Standard SQL/PSM. In fact, almost too close -- it has some stuff that other SQL products don't have yet. For example:

 BEGIN
 SELECT MIN(node_id)
   INTO nxt
   FROM Tree AS C
  WHERE C1.parent_id = last_visited
    AND C1.nodenum IS NULL;
 EXCEPTION WHEN NO_DATA_FOUND
           THEN SET nxt = NULL;
 END;   would get the same effect as just:

 SET nxt

  • (SELECT MIN(node_id) FROM Tree AS C WHERE C1.parent_id = last_visited AND C1.nodenum IS NULL);

Other products don't have the singleton select or the exception handling, which are standard. But we can take advantage of the rules about aggregate functions and emptry sets to get it all into one statement.

And we should get rid of the sql%rowcount somehow. That one is proprietary. Can we get a WHILE condition at the start of the loop based on the size of the tree?  

>> One only needs a stack if a node can have multiple paths to it from
root. <<

But then it is not a tree.

>> I have another idea for a more parallelizable algorithm based on
some
stuff I did a few years ago. I'll see if I can reconstitute the stuff I
need to try that out. <<

Neat! Received on Mon Mar 04 2002 - 22:56:53 CET

Original text of this message