These are known Oracle tree extensions. They will work well, but it is NOT portable. Also connect by prior is a kind of limited hack for working with trees. It will work as long as you stick with oracle.
This subject is elaborated upon some in the Celko book. (I just like the book, don't take my recommendation of it repeatedly wrong :) It also shows a few tricks to get around the start with/connect by prior limitations. Instead of using QoQ you can just use a correlated sub query. Good Luck! Thanks Jeremy Allen elliptIQ Inc. -----Original Message----- From: Jason Blum [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 30, 2001 8:13 AM To: CF-Talk Subject: RE: *** Complicated SQL riddle Am going to try to pick up a copy of Celko's book. Not sure how, but I think I have something that works: Had to include a column in the table called level, which then allows me to accordingly indent each row that is outputted from this query: select * from TBL_EMPLOYEE start with employeeid=1 connect by prior employeeid=bossid and it seems to work beautifully! Oh, then in the output, each line is prefixed with #RepeatString("<img src=spacer.gif>", LEVEL)# so that the level determines how many spacers prefix each line... ...Anyway, thanks for your help everybody - most of it was over my head - but I got a working solution and will get Celko's book! -Jason > ---------- > From: Jeremy Allen > Reply To: [EMAIL PROTECTED] > Sent: Monday, October 29, 2001 3:51 PM > To: CF-Talk > Subject: RE: *** Complicated SQL riddle > > Actually, > > One person did the hard work so we don't have to :) > > http://www.codebits.com/ntm/ > > Also check here: > > http://www.intelligententerprise.com/001020/celko.shtml > > > Dave Medinets has written a set of "proof of concept" > stored procedures. They cover Inserting, Deleting, and > yes even MOVING nodes around the tree. Moving properly > handles moving all sub children to any place within > an NTM tree. > > Another advantage > that causes the tree to mimic your more traditional > Ye old computer science text book N-Ary tree is that > there is an implied ordering to all nodes in the tree, > whereas with the old employee/boss trick there is no > implied order. (I like calling the employee/boss relationship > the Parent-Child model) > > I would bore you guys with stored procedures I have > written but there is no need. I have also adapted > the stored procedures found at the URL for a production > environment. Take a look at them and you wont really > need to figure out how it works unless your bored. > > The Celko book explains how it works in words infinitely > easier to grasp than my own words. > > Not to sound like a parrot but the model really does > work and while it may be a little tricky it is just a > case of basic logic that a good afternoon of stored > procedure coding and adapting can cure. > > For a fun thought of the day, try implementing N-Ary > trees in JavaScript :) > > Thanks > > Jeremy Allen > elliptIQ Inc. > > > > -----Original Message----- > From: Deanna Schneider [mailto:[EMAIL PROTECTED]] > Sent: Monday, October 29, 2001 3:28 PM > To: CF-Talk > Subject: Re: *** Complicated SQL riddle > > > Hey Jeremy, > While pulling data out of the nested set model is easy, getting it in > is not > so easy. Care to throw out some examples of that? > > -D > > > > Deanna Schneider > Interactive Media Developer > [EMAIL PROTECTED] > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
