-1

I was able to successfully write a query that returns a hierarchy, which would be 7 levels deep. I want to add a column that has a "stop" value for each row. Please see the example here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/img/sqlrf002.gif

Referencing the hierarchy above for example, I want all of the nodes that roll up to node 2, to each have a "stop value" of 2. This needs to be set based on some criteria. For example, if Node 9 doesn't meet the criteria, the "stop locations for all nodes reporting to Node 9 would show Node 1. The criteria would only ever be done at level 2.

wrestler
  • 3
  • 1
  • 3
    Life will be easier for everybody if you posted a table structure with sample data and demonstrated your requirements using example output derived from that data. – APC Jul 08 '19 at 18:46
  • Welcome to stack overflow. In order to better assist you there are general guidelines that will help us help you. Please see the link. As mentioned... more information or samples of your issue will help us visualize what you're discussing. https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/ – krewsayder Jul 08 '19 at 20:05

1 Answers1

0
with criteria(guy, criterion) as (
  select 2,1 from dual
)
, data(son, dad) as (
  select 2,1 from dual union all
  select 7,1 from dual union all
  select 9,1 from dual union all
  select 3,2 from dual union all
  select 4,2 from dual union all
  select 8,7 from dual union all
  select 10,9 from dual union all
  select 12,9 from dual union all
  select 5,4 from dual union all
  select 6,4 from dual union all
  select 11,10 from dual
)
, hier as (
  select connect_by_root(dad) level_1_guy,
    connect_by_root(son) level_2_guy,
    level lvl,
    son
  from data
  start with dad = 1
  connect by dad = prior son
)
select h.son,
  case when c.criterion = 1 
    then h.level_2_guy 
    else h.level_1_guy 
  end stop_value
from hier h
left join criteria c on h.level_2_guy = c.guy
where lvl > 1;

       SON STOP_VALUE
---------- ----------
         3          2
         4          2
         5          2
         6          2
         8          1
        10          1
        11          1
        12          1
Stew Ashton
  • 1,499
  • 9
  • 6