We have a data model with a “components” table that is linked to itself through an “components relations” intersections table, one that describes instances where a component is part of another component. Note that a component may contain many other components and a component can be part of many components. Instead of component, you may also read ingredient when discussing recipees, subject when discussing a thesaurus or book index, social clustering etc. I ran into the “bill of materials” recently while working on our new Skill Management application, where all skills of the AMIS staff are recorded and maintained. We are talking about some 70 developers with skills in over 600 tools, technologies, roles and versions. Having to select one ‘subject’ or ‘component’ such as Hibernate Release 3 or PL/SQL Collections from the list of 600 items can be a pain. Especially since this list is really organized quite hierarchically. In the above two cases, what you actually select is: Java/J2EE – OO/R and Persistency – Hibernate – Hibernate Release 3 or Oracle – Oracle Database Development – PL/SQL – PL/SQL Collections. Both for maintaining the list of “knowledge components” as for selecting elements from this list, it would be very convenient if we present the list as a tree. Let’s see how to do that.

Using the Connect By query for Bill of Materials

The Oracle CONNECT BY clause is typically used to present data in the form of an hierarchy. Usually the CONNECT BY makes use of a self-referencing foreign key on the table whose records are the nodes in the tree. However, in this particular case the hierarchy is defined across two tables.

We will first combine the two tables in an in-line view. We are selecting all component nodes: every occurrence of a component as child in a relation with another component. To ensure we also select root-nodes – components that only exist in the tree as top-level parent – we use the left outer join.

What we will do is first select all nodes from our Components-tree. Then we will add a marker column to each node in the tree, indicating whether or not that node satisfies the search conditions. Next we build the components tree again, BOTTOM UP, starting from all marked nodes. This bottom up tree contains all selected Components – we start building the tree on those nodes after all – as well as all their ancestors, since the tree is built from the selected nodes all the way to the root. Finally, we reconstruct a tree from all selected nodes – either because of ancestor-ship or because they directly satisfy our search condition.

with component_nodes as -- all component nodes anywhere in the tree, including the root level without leafs/children (because of the left outer join)
( with component_nodes as
(select cpt.id
, cpt.name
, rel.cpt_part_of
from components cpt
left outer join
component_relations rel
on (cpt.id = rel.cpt_containee)
)
, selected_components as -- all components with a marker column for those that satisfy the search requirement: somewhere in their name appears the string java (case insensitive)
( select cpt.*
, case
when lower(name) like '%java%'
then 'X'
end marker
from component_nodes cpt
)
, tree_nodes as -- build a tree, bottom up, starting only from the nodes that passed the requirements (marker=X)
( select distinct
id
, cpt_part_of
, name
from selected_components
connect
by prior cpt_part_of = id -- note that the connect by condition is the reverse from what you usually find
start with marker is not null --start with the nodes that qualify and work upwards to the root component
)
select lpad(' ', 2+level * 4)||name Component_Nodes -- given all the nodes in the tree (qualifying nodes as well as their ancestors), now build the tree properly, top to bottom
from tree_nodes
connect
by prior id = cpt_part_of -- cpt_part_if is the reference to the parent node
start with cpt_part_of is null -- start from the root nodes; the cpt_part_of parent reference is null obviously
/

meta

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 206 other subscribers

Email Address

About

AMIS is internationally recognized for its deep technological insight in Oracle technology. This knowledge is reflected in the presentations we deliver at international conferences such as Oracle OpenWorld, Hotsos and many user conferences around the world. Our AMIS Technology Blog, the most referred Oracle technology knowledge base outside the oracle.com domain. However you arrived here, we appreciate your interest in AMIS. Link to our Google+ Profile AMIS