Steve, I am happy you were able to excise one of your devils. I would be happy to someday see your command files. I am a big fan of Cleko but haven't been able to spend the time to adapt his code to RBase syntax.
Jim Bentley --- "Wills, Steve" <[EMAIL PROTECTED]> wrote: > Ben, it took a bit of effort, goin' at it in fits and starts, > but I did get it "whipped". > > Below, for any who may be interested, is the result of my > effort, with thanks to Ben (and Celko). If you look at this, > you might be able to see why it could be useful for data that > is hierarchical in structure, but in a 2-D database table. > Although there is more to it, if I were to associate, let's > say, Sales Tax Revenues for all rows at level 4, municipality, > then I could select Tennessee, at level 2, and run a query > with a 'SUM(SalesTaxRevenues) WHERE NodeLeftSide > 3 and > NodeRightSide < 14 and NodeLevel=4 (Nodelevel restriction > necessary to avoid double-counting if records at other > NodeLevels also have SalesTaxRevenue data.). > > Given this simple/simplistic example, it would be easy to do > this without all this hierarchy stuff. However, what if you > had all the municipalities and/or counties in the state of > Tennessee? There are 95 counties in the state. What this > does is allow for the querying of branches/sub-branches of the > organizational tree. By using a query, view, (temp_)table > that joins/unions whatever data elements you might have with > the hierarchical (Level, Left, Right) elements, you can create > and execute queries that are difficult (or impossible) to do > otherwise. > > I'm sure that some slick, cool query forms could be > implemented, including the use of the DB Tree View control. > > I know my explanation is wholly insufficient, but, trust me, > if you encounter hierarchical data, ask yourself how you would > figure out the total whatever for this sub-unit and it's > sub-units ... then, ask me and I'll share the source from my > RMD file. I'm not bragging, please believe me. As smart as I > think I am, I agree with Jim Bentley (and perhaps Ben, too) > that some of this stuff is "mind-boggling", so I have had my > challenges, including the fear of not being able to understand > it. Now that I have a rough understanding and code that makes > my hierarchical data useful, i.e. query-able, I'm a happy > camper. > > (Let me also add that all this is for simple hierarchies, with > 1 parent only. I'm not yet ready for hierarchies with 2:N > parents ...) > > My current employer has some 537 unique (sub-)units in it, > from the root down (or up, if you prefer) with a total of 8 > levels/generations in its organizational tree. I have already > been asked about how much whatever does a certain > non-root-node and its children have? > > --... > > [MY_UNIT_HIERARCHY_AS_NESTED_SETS] > > GPU_ID GPU_PARENT_ID > HasChildren NodeLevel NodeLeftSide NodeRightSide > ----------------------------------- > ----------------------------------- ----------- ---------- > ------------- ------------------ > USA 0 > Y 1 1 30 > ARKANSAS USA > Y 2 2 9 > CRITTENDEN ARKANSAS > N 3 3 4 > PULASKI ARKANSAS > N 3 5 6 > WHITE ARKANSAS > N 3 7 8 > MISSISSIPPI USA > Y 2 10 13 > DESOTO MISSISSIPPI > N 3 11 12 > TENNESSEE USA > Y 2 14 29 > FAYETTE TENNESSEE > N 3 15 16 > SHELBY TENNESSEE > Y 3 17 26 > COLLIERVILLE SHELBY > N 4 18 19 > GERMANTOWN SHELBY > N 4 20 21 > MEMPHIS SHELBY > N 4 22 23 > MILLINGTON SHELBY > N 4 24 25 > TIPTON TENNESSEE > N 3 27 28 > > NOTES: > (1) 'GPU' = Geo-Political Unit, i.e. a Nation, State, County, > Municipality, Etc > (2) In this example, the root-node is USA, identified by '0'. > The root is the ultimate progenitor, if you will, the > "parent-of-parents". Other means for identifying the root > could be used. The root could have itself as its own parent, > some other text value, or even NULL, although I would probably > shy away from NULL, even though it might actually be logically > justified here. > (3) NodeLevel could be viewed as "generation". It is the > current level in the hierarchy. > (4) Left and Right values indicate the breadth of successive > generations. See how USA has 1..30, as, being the root-node, > it encompasses the entire span of the tree. > (5) Left and Right values are independent of how the records > are uniquely identified. The Level, Left, Right values are > just the numbers that identify the structure of the tree. > > > [MY_UNIT_HIERARCHY] > > GPU_ID GPU_PARENT_ID > ----------------------------------- > ----------------------------------- > USA 0 > TENNESSEE USA > ARKANSAS USA > MISSISSIPPI USA > SHELBY TENNESSEE > TIPTON TENNESSEE > FAYETTE TENNESSEE > MEMPHIS SHELBY > MILLINGTON SHELBY > COLLIERVILLE SHELBY > GERMANTOWN SHELBY > PULASKI ARKANSAS > WHITE ARKANSAS > CRITTENDEN ARKANSAS > DESOTO MISSISSIPPI > > NOTES: > (1) This is simple, really; it's just a 2-column table with > the GPU and its parent. > > Again, if anyone wants, I'll elaborate all I can, which is > only a bit, on why this is "a good thing" (I use that but I'm > not much of a Martha Stewart fan) and I'll provide my RMD > file, too. > > Steve in Memphis > > > > > > > > > > > > > > > > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of Ben Petersen > Sent: Wednesday, March 12, 2008 10:56am 10:56 > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Hierarchical Data ... > > > > 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. > > As a friend of mine once said to a similar comment, "Ben, > that's just sad" <g>. > > I regret to say that when I've used this logic in the past I > had the luxury of starting from scratch (no data conversion), > and most recently in VB, but with different objectives, so I > can't offer much help. Looking at his "push down stack > algorithm" just makes my head hurt w/o more time to digest it. > > > I can offer this *very* modest advice, fwiw. It became much > easier for me to internalize when I reflexively knew that if > the two indexes were sequential the data item had no children, > otherwise other data sets were encapsulated. I know it's > obvious... and I can't explain why it seeing it just that way > greased my mental skids, but there you are (more sadness, I > guess <g>). > > I had originally seen this method in a posting on this list > long ago. I just Googled "celko sql tree" to get something > explanatory for you. There are a bunch of matches and I recall > seeing this applied in a number of different ways when I > looked into it some years ago, so there may be more help > there. But, it sounds like you've almost got it whipped. > > Ben > > > === message truncated === 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
