From owner-oracle-l@CCVM.SUNYSB.EDU Sun Mar 19 18:41:06 1995 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65/1.1.8.2/01Aug94-0142PM) id AA29409; Sun, 19 Mar 1995 18:41:02 -0500 Message-Id: <9503192341.AA29409@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by CCVM.sunysb.edu (IBM VM SMTP V2R2) with BSMTP id 7695; Sun, 19 Mar 95 18:06:03 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 8347; Sun, 19 Mar 1995 15:56:36 -0500 Date: Sun, 19 Mar 1995 15:56:36 -0500 Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: Roel Duijnhouwer Subject: Re: Traversing hierarchy with "connect by..." clause X-To: ORACLE-L@SBCCVM.bitnet To: Multiple recipients of list ORACLE-L X-Orig-Date: Fri, 17 Mar 1995 08:59:03 +0000 K. Huguley wrote ... > I have a table with 2 columns (descendant, ancestor) in which > I have stored thesaurus data (narrower term, broader term). > Now, I need to retrieve the hierarchy trees using ORACLE's > "start with...connect by...." clauses of the SELECT stmt. > I have no problems moving down the tree for a specific > term (i.e., collecting all BROADER TERM relationships), but I am > unable to move up the tree (to collect NARROWER TERM relationships) > for that same term. Do I have to use PL/SQL to accomplish this, > or can I do it with a simple SQL stmt? > I would appreciate any information - the ORACLE manuals are very > vague. > > Thank you, > NASA Center for AeroSpace Information I think a simple SQL stmt can do it..... .. .. SQL> desc a "My table" .. .. Name Null? Type .. ------------------------------- -------- ---- .. PARENT CHAR(10) .. CHILD CHAR(10) .. .. .. SQL> select * from a; "My tables contents" .. .. ROOT .. ROOT AAA .. ROOT BBB .. ROOT CCC .. AAA AAA-one .. AAA AAA-two .. AAA AAA-three .. BBB BBB-one .. BBB BBB-two .. CCC CCC-one .. CCC CCC-two .. CCC CCC-three .. CCC CCC-four .. .. 13 rows selected. .. .. SQL> column parent format a25 .. SQL> column child format a25 .. SQL> "moving down the tree" .. SQL> select level .. 2 , lpad(' ',2*level)||parent parent .. 3 , lpad(' ',2*level)||child child .. 4 from .. 5 a .. 6 connect by .. 7 prior child = parent .. 8 start with .. 9 parent is null .. 10 / .. .. 1 ROOT .. 2 ROOT AAA .. 3 AAA AAA-one .. 3 AAA AAA-two .. 3 AAA AAA-three .. 2 ROOT BBB .. 3 BBB BBB-one .. 3 BBB BBB-two .. 2 ROOT CCC .. 3 CCC CCC-one .. 3 CCC CCC-two .. 3 CCC CCC-three .. 3 CCC CCC-four .. .. 13 rows selected. .. .. SQL> "Moving up the tree" .. SQL> select level .. 2 , lpad(' ',2*level)||parent parent .. 3 , lpad(' ',2*level)||child child .. 4 from .. 5 a .. 6 connect by .. 7 child = prior parent "look at PRIOR" .. 8 start with .. 9 child = 'CCC-two' .. 10 / .. .. 1 CCC CCC-two .. 2 ROOT CCC .. 3 ROOT Roel Duijnhouwer email: roel.duijnhouwer@pagv.agro.nl Research Station for Arable Farming and Vegetables Lelystad, The Netherlands.