Re: Hierarchies
In article <9273@emory.mathcs.emory.edu> jgordon@ssf-sys.DHL.COM (Jim Gordon)
writes:
> Hi all,
>
> I have an SQL problem that I have run into and I am wondering if
> anybody has any solutions to it. The problem is the ordering of a
> hierarchy on a report. I have two table one of which contains the
> parent and child relationships between entries in the other table.
>
> Eg table1
> entry
>
> table2
> parent-entry
> child-entry
>
> I need to produce an ordered lists starting from the Top of the
> Hierarchy in the following orders:-
>
> Report 1 Report 2
> entry parent child
>
> Top Top A
> A Top B
> 1 Top C
> 1A A 1
> 1B A 2
> 1C B 5
> 2 C 4
> 2A 1 1A
> 2B 1 1B
> B 1 1C
> 5 2 2A
> 5A 2 2B
> 5B 4 4A
> C 5 5A
> 4 5 5B
> 4A
You are describing the problem I know as "the bill of materials"
problem.
> Now I have an example from an Oracle ... database
> which uses a special function. Eg:
>
> select LPAD(' ',2*LEVEL) || child-entry MODEL
> from table2
> connect by prior child-entry = parent-entry
> start with parent-entry = "Top"
Oracle's SQL apparently has special support for bill-of-materials
type processing, support which as you note, is not in ANSI SQL
or in Informix SQL.
> Does anybody have any Informix code that can produce this ordering or
> have any suggestions on how I might code the select statements.
Working in ISQL/ACE it is difficult. You can solve for a *fixed*
number of generations using outer joins. For instance, using your
data (WARNING:UNTESTED SQL -- check my syntax!):
select g1.child, g2.child, g3.child, g4.child
from table2 g1 outer(table2 g2 outer(table2 g3 outer table2 g4))
where g1.parent = "Top"
and g1.child = g2.parent
and g2.child = g3.parent
and g3.child = g4.parent
something like that...:-)
However to solve the general case of an unknown number of generations
you need to apply procedural (recursive) logic. (plug) There is a fully
worked example in Informix-4GL in our manual, "4GL By Example"
available from an Informix rep near you... (end plug)