Steve, I have also read Cleco's books. Some of it is mind boggling. If you post the complete code perhaps we can spot your problem.
The hirarical/tree problem is almost as ald as computers. IBM first tackled the problem with it's Bill-of-Material (BOMP) program is the IBM 360 Mainframe days. I became intimetly involved with that program, It's successor DBOMP because the Software International General Ledger used it as the database for that product way back the late 1960 and early 1970's. Jim Bentley --- "Wills, Steve" <[EMAIL PROTECTED]> wrote: > Ben, your message bolstered my courage to tackle this - kinda' > humorous that a discussion of hierarchical data and a > nested-sets implementation might "inspire" someone. So, late > yesterday, I got with it and made some real progress. I was > particularly intrigued by some of the (seemingly easy SQL) > things one could do with this, per Celko's examples, once the > table was built. > > However, I'm having a problem. I have most of Celko's books > and I grabbed the article you linked to in your message. I > followed the logic (mostly) and the (pseudo-)SQL. No problem > loading my 2 hierarchical data into the NODE_TREE table. Then > I looked at the code used to build the NODE_STACK table, > which, unfortunately, are not exactly equivalent in my 2 books > containing it as well as the article. I "ported" it to RBase > as best I could, testing each statement as I went. (Table > def's follow with my naming conventions.) > > Table: NODE_TREE No Lock(s) > Descr: -0- > > No. Column Name Attributes > --- ------------------ > ------------------------------------------------------ > 1 NodeID Type : TEXT 10 > 2 NodeParentID Type : TEXT 10 > Current number of rows: 0 > > > Table: NODE_STACK Cursor Lock(s) > Descr: -0- > > No. Column Name Attributes > --- ------------------ > ------------------------------------------------------ > 1 NodeStackTop Type : INTEGER > 2 NodeID Type : TEXT 10 > 3 NodeLeftSide Type : INTEGER > 4 NodeRightSide Type : INTEGER > Current number of rows: 537 > > When it executes though, something goes awry. All my > NODE_TREE records are INSERTed INTO NODE_STACK, just fine. > Their NodeLeftSide values also appear correct. Initially, via > a BROWSE, the values for both NodeStackTop and NodeRightSide > appeared to have been UPDATEd correctly. Then, the loop never > ends, ultimately decrementing NodeStackTop and incrementing > NodeRightSide as it runs. > > I can't figure it out as of yet, but I'm gonna' stay with it > until I do. Might you have any ideas or suggestions? Maybe > you would know which version of Celko's code is the correct > one? > > Thanks, > Steve in Memphis > > > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of Ben Petersen > Sent: Tuesday, March 11, 2008 10:07 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Hierarchical Data ... > > Steve, > > I've found Celko's "nested sets" logic perfectly usable, and > has the > advantage over most models of being able to "prune and graft" > your tree > if you have the need. I agree with his comment that arrays > make this > *very* easy, but they are by no means a requirement. An > example is here: > http://www.intelligententerprise.com/001020/celko.jhtml > > Ben > > > Wills, Steve wrote: > > Almost hate to open this can of worms, Pandora's Box, > whatever, but playing around with one of Razzak's examples > related to the DB Treeview Form Control, I feel compelled. > So, lemme' ask, what do y'all do w/in RBase when dealing with > hierarchical data, such as organizational charts for units, > staff, biological classification, etc? > > > > For example: > > > > UnitID UnitName ParentUnitID HasChildren > > ------ --------------- ------------ ----------- > > 000000 Global NULL||000000 Y -- Either > NULL or UnitID=ParentUnitID would serve to indicate root node > > 110000 Europe 000000 Y > > 111000 Great Britain 110000 Y > > 111100 York 111000 N > > 120000 Germany 110000 Y > > 121000 Hesse 120000 Y > > 121100 Darmstadt 121000 N > > 122000 Bavaria 120000 Y > > 122100 Würzburg 122000 N > > 122200 München 122000 Y > > 122210 Bierhalle 122200 Y > > 122211 Augustiner 122210 N > > 122212 Englische 122210 N > > 200000 North America 000000 Y > > 220000 USA 200000 Y > > 221000 Tennessee 220000 Y > > 221100 Memphis 221000 Y > > 221110 Sports Arenas 221100 Y > > 221111 Autozone Park 221110 N > > 221112 Fedex Forum 221110 N > > > > (NOTE: There is no implicit||explicit embedded intelligence > in the ID values; that would be a different can of worms.) > > > > Given the above (rather arbitrary) hierarchy, I would like > to be able to gather data, including aggregating functions > (COUNT, SUM, etc) starting at any level/node and look either > up or down from that starting point. Using the example, I > might want to know how many children roll-up to Bavaria or, > from Memphis, how many sports arenas are in the data. Of > course, the real usefulness would occur when I also used data > associated with, say Bierhalle, as in how many gallons (well, > liters) of beer were sold or with Memphis, Sports Arenas, how > many seats are available. > > > > I've been reading some Celko about this and even he - > whether you hate/love/don't care about Celko - says that his > solution, sans special/proprietary hierarchical operators, is > functional but inelegant and not terribly efficient. It also > appears that some DBMS vendors have implemented special > hierarchical operators, such as WITH (DB2), CONNECT (Oracle), > etc. > > > > Having played with the DB Treeview control yesterday and > having quickly mapped our own organization table (in)to it, I > thought, "This is really cool." Now, however, I'm looking for > a way to select a node and walk up/down from that point, > grabbing and/or aggregating data associated with the node and > direction in order to display it on the same form. This isn't > yet an enhancement request, but given RBTI's implementation of > this control, it seems an obvious next step. I also suspect > that it would take Razzak and RTeam little time to do this in > a fashion compliant with current SQL standards. I also have > no doubt that at least one among us would be EVER SO GRATEFUL > ... > > > > Thanks, > > Steve in Memphis > > > > > > J. Stephen Wills > > Program Manager, Research Informatics > > Office of the Vice Chancellor for Research > > University of Tennessee Health Science Center > > 62 S. Dunlap, Suite 400 > > Memphis, TN 38163 > > Office: 901-448-2389 > > FAX : 901-448-7133 > > > > > > > > > > > Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293 ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
