Skip to Main Content
  • Questions
  • Vertical and Horizontal Hierarchical Chains

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: April 28, 2021 - 9:18 pm UTC

Last updated: May 17, 2021 - 2:12 pm UTC

Version: 12.0.1.2

Viewed 100+ times

You Asked

Hi Chris,

Table T stores parts relationships of four types - Unilateral, Bilateral, Marketing/Promotional, and KITs, and the requirement is to identify the items relationship chain.

The relation type 'S' means superseded and is combined with relation direction 'O' unilateral (i.e. A -> B -> C) and 'T' bilateral (D -> E <-> F)

The relation type 'M' are multiple parts required for marketing/promotional purposes. Relation direction doesn't matter here and there can be only one parent in such relation (Y -> Y1, Y -> Y2).

The last relation is identified by the component column having a value 'C' meaning the component type of relation, relation direction doesn't matter here, and there can be only one parent in such relation.

SELECT 'X', 'S' , 'X1', NULL, 'C' FROM DUAL UNION ALL i.e. A row with relation_type 'S' and component as 'C' denotes a major part of the KIT.

SELECT 'X', 'R' , 'X2', NULL, 'C' FROM DUAL UNION ALL i.e. A row with relation_type 'R' and component as 'C' denotes a minor part of the KIT.

X -> X1

X -> X2
An "item" is either a kit, recognized by relation type 'M' or by component = 'C', or else it is a "single item" (recognized by relation type different from 'M' and null component. In the case of kits, I want the query to return only the immediate children (direct descendants), while for "single items" I want to see the entire hierarchy of descendants.

In a unilateral relationship, you can only move forward in the chain but in a bilateral relation, you must go a step back and check if the item in the previous step also has bilateral relation, and if yes again go a step back until you reach a unilateral relation and then start moving forward e.g. E <> F.

Expected output -

When passed item A as parameter then -

parent child

A B

A C B to A row should be ignored here because that is an incorrect recursive relationship.

When passed item B as parameter then -

parent child

B C

When passed item D as parameter then -

parent child

D E

D F

When passed item E as parameter then -

parent child

E F

When passed item F as parameter then (because of E <--> F traverse back)-

parent child

E F

When passed item Y as parameter then (Check the only row of relation type M for the part)-

parent child

Y Y1

Y Y2

When passed item X as parameter then (Check the only row of the component as C for the part)-

parent child

X X1

X X2

The sample data and code in the live SQL link give desired results except in the below two cases which I want to manage in solution.

Case 1 - When the is full bilateral relation P <> Q <> R <> S <> T, the expectation is as below when you pass T item -

Parent Child
P Q
P R
P S
P T


How to add order by in output?
How to remove the distinct logic from the query in any way?




with LiveSQL Test Case:

and we said...

I'm not sure I understand the requirements fully.

For example, what should be the output if the input is S? The same as T or something else? And what happens if there's an edge between T and P?

Also B has B -> A and B -> C, so why do you only want B -> C if this is the input?

In any case, here's an outline of a solution which should get you started:

- From the input node, try and go up and down the tree according to the relationship types
- When going down, the overall parent is the root
- When going up, the overall parent is the leaf (what happens if a path has many leaves?)
- While doing so, exclude any edges with the same vertices as the current going in the other direction
- Union the results of the two trees together
- Find the overall parent by taking:
- The final leaf from the UP branch if present
- The down root if there's no up

You can do the final operation by using last_value, sorting the rows first by UP/DOWN, then by reverse order for the UP and tree order for DOWN:

with up ( rt, nd, lvl, direction ) as (
  select  case when connect_by_isleaf = 1 then v1 end rt,
          v2, level, 'UP' direction
  from    t 
  where   connect_by_iscycle = 0 
  start   with v2 = :item 
  and   relation_direction = 'T' 
  and ( v1, v2 ) not in ( select v2, v1 from t )
  connect by nocycle relation_direction = 'T' 
  and prior v1 = v2 
  and ( v1, v2 ) not in ( select v2, v1 from t )
), down ( rt, nd, lvl, direction ) as (
  select  connect_by_root(v1), v2, level, 'DOWN' direction
  from    t 
  where   connect_by_iscycle = 0 
  start   with v1 = :item 
  connect by nocycle prior relation_type != 'M' 
  and prior component is null 
  and v1 = prior v2 
  and ( v1, v2 ) not in ( select v2, v1 from t )
), tree as (
  select * from down
  union all
  select * from up
)
  select t.*, 
         first_value (
           rt
         ) over ( 
            order  by case 
              when direction = 'UP' then 1 else 2 
            end, case 
              when direction = 'UP' then -lvl else lvl
            end    
         ) parent
  from   tree t
  order  by case 
    when direction = 'UP' then 1 else 2 
  end, case 
    when direction = 'UP' then -lvl else lvl
  end;


This mostly gives the output you requested (though input B and you'll get B -> A and B -> C) and I've guessed that when passing S, R, etc you want the same output as T.

The connect by clauses are likely to be specific to the data you've passed though; I'm unconvinced these will generalize to your full data set.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.