Re: hierarchical tree problem (join)

From: Christopher Beck <christopher.beck_at_oracle.com>
Date: Fri, 10 May 2002 11:05:20 -0400
Message-ID: <YjRC8.6$9K.27_at_news.oracle.com>

Davy,

I assume that you are not using 9i since you CAN write connect by queries with joins!

scott_at_ORA9I.WORLD> l
  1 select lpad( ' ', level*2, ' ' ) || e.ename ename,   2 d.dname
  3 from dept d, emp e
  4 where d.deptno = e.deptno
  5 start with ename = 'KING'
  6* connect by prior empno = mgr
scott_at_ORA9I.WORLD> /

ENAME                DNAME
-------------------- --------------
  KING               ACCOUNTING
    CLARK            ACCOUNTING
      MILLER         ACCOUNTING
    JONES            RESEARCH
      FORD           RESEARCH
        SMITH        RESEARCH
      SCOTT          RESEARCH
        ADAMS        RESEARCH
    BLAKE            SALES
      ALLEN          SALES
      MARTIN         SALES
      JAMES          SALES
      TURNER         SALES
      WARD           SALES

14 rows selected.

A great reason to upgrade ;-)

But in older versions we can still accomplish the same thing using an inline view.

  1 select lpad( ' ', lev*2, ' ' ) || e.ename ename,   2 d.dname
  3 from dept d,

  4         ( select ename, deptno, level lev
  5             from emp
  6            start with ename = 'KING'
  7          connect by prior empno = mgr ) e

  8* where e.deptno = d.deptno
scott_at_ORA8I.WORLD> /

ENAME                DNAME
-------------------- --------------
  KING               ACCOUNTING
    JONES            RESEARCH
      SCOTT          RESEARCH
        ADAMS        RESEARCH
      FORD           RESEARCH
        SMITH        RESEARCH
    BLAKE            SALES
      ALLEN          SALES
      WARD           SALES
      MARTIN         SALES
      TURNER         SALES
      JAMES          SALES
    CLARK            ACCOUNTING
      MILLER         ACCOUNTING

14 rows selected.

Just do the connect by query in an inline view and then join those results to the other table.

hope this helps.

chris.

--
Christopher Beck, Principal Technologist, Oracle Corporation
christopher.beck_at_oracle.com
Beginning Oracle Programming,
http://www.amazon.com/exec/obidos/ASIN/186100690X
Opinions are mine and do not necessarily reflect those of Oracle Corp



"The Black Unicorn" <tbu_at_pandora.be> wrote in message
news:pan.2002.05.10.11.50.28.773583.2169_at_pandora.be...


> Hi,

>

> I'm having a problem with a hierarchical tree item that I want to use for


> a school project. I'm fairly new at Developer and can't solve this one.

>

> I have two tables :

> resdata :

> DATA_ID, NAME, DESCRIPTION

> resdatalink :

> PARENT_ID, CHILD_ID

>

> Any DATA_ID can have more than one entry in the resdatalink table, so

> just adding a parent field in resdata is not an option. What I

> want is to create a hierarchical tree from an outer join of resdata and

> resdatalink.

>

> Unfortunately, hierarchical queries don't allow for joins. Working

> through a view doesn't work either. I've tried to work with record groups

> : create one group containing the joined tables, and then one with the

> hierarchical query. However, I haven't found out how to query a record

> group in Developer.

>

> Can anyone help me with this one, I'm running out of ideas. Any help

> would be greatly appreciated.

>

> TIA

> Davy Herben

Received on Fri May 10 2002 - 17:05:20 CEST