hierarchical tree

From: Martin Burkert <martin.burkert_at_a1plus.at>
Date: Fri, 14 Jun 2002 09:30:31 +0200
Message-ID: <aec67n$c0f$1@fstgss02.tu-graz.ac.at>

Hi!

I would like to create a hierarchical Tree with Oracle Forms.

To do this, I have to create a recursive sql-Statment. But there is a small problem. The table I would like to print out in the tree could have more then one root-component. The root component is never lower_component.

Table Tree

Upper_component Lower_component
Comp 1 Comp 2
Comp 1 Comp 3
Comp 3 Comp 5
Comp 3 Comp 6

This means, that the root Component includes Comp2 and Comp3, and Comp3 include Comp5 and 6.
Comp1 is NEVER a lower_component
(e.g. 'Null' Comp1)

my statement:
select decode (level, 1, 1, 1),LEVEL, lower_comp, NULL, lower_comp from composition
start with upper_comp = 'comp1'
connect by prior lower_comp = upper_comp

this statement has the output:

decode level lower lower
1 1 comp2 comp2
1 1 comp3 comp3
1 2 comp5 comp5
1 2 comp6 comp6

what I want is the root-component, so that the output is...

decode level lower lower
1 1 comp1 comp1
1 2 comp2 comp2
1 1 comp1 comp1
1 2 comp3 comp3
1 3 comp5 comp5
1 3 comp6 comp6

However it is not posssible to create a "dummy"-entry, so that comp1 has a parent-entry!!!
Has somebody an idea to realize this with an sql-Statement?

Note: it is possible to find the root-components with a sql-statement

(select upper_comp from composition)

       minus
      (select lower_comp from composition)

greeting Martin Received on Fri Jun 14 2002 - 02:30:31 CDT