Oracle complex query with hierarchy

Question

Following is my Sample data1

CP | NP |MC |DS |NNP
--- ---- --- -- ---
A  |  B |   |   |C
C  |  D | XX|YY |E
E  |  F | ZZ|11 |

Expected Output1

NP |MC |DS 
--- ---- --- 
 B | ZZ|11

Here column "CP" and "NNP" are hierarchical (i.e.) we will start with query like below, for a particular NP and then get the corresponding MC and DS values using the below logic

select MC, DS from tabl1
where NP=B

first look at value A from column CP and then see if column NNP as a corresponding value, here it is C value in column NNP
if there is a corresponding values form column CP present in NNP then look at column CP for C value and then see if NNP as corresponding value here it is E value in column NNP (repeated these steps until we do not find a corresponding entry in NNP column for column CP)
fetch E value corresponding values for column MC and DS

Following is my Sample data2

CP | NP |MC |DS |NNP
--- ---- --- -- ---
A  |  B | 96|KK |C
C  |  D | XX|YY |E
E  |  F | ZZ|11 |

Expected Output2 (if the corresponding root values are not null then we need to fetch those as well

NP |MC |DS 
--- ---- --- 
 B | 96|KK
 B | ZZ|11

You might be interested by Hierarchical Queries

Luc M
– Luc M

2021-02-09 14:22:00 +00:00
Commented Feb 9, 2021 at 14:22 — Luc M
– Luc M, Commented Feb 9, 2021 at 14:22

score 1 · Accepted Answer · 2021-02-10 22:16:25Z

1

Direct application of hierarchical (connect by) query:

select  connect_by_root(np) as np, mc, ds
from    sample_data
where   connect_by_isleaf = 1   -- OR:  nnp is null   (IF NEEDED, SEE BELOW)
start   with cp = 'A'
connect by cp = prior nnp
;

In your output, in column NP you have the value B, which is the value in the "root" row (the starting row). This is where you can use connect_by_root. All the other values are from the leaf row ("last" row in the hierarchy). Here is where we need to use the filter connect_by_isleaf = 1 - the value is 0 for non-leaf rows (rows that have at least one child), and 1 for leaf rows.

Note that "leaf" means no further children. This can happen either if NNP is null OR if it is not null but the value is not found in the CP column anymore. If you only want leaf rows where NNP is null, you can change the where clause as shown in the code above.

EDIT The OP requested a modification - the output should also include the root row, in addition to the leaf, but only if either the mc or the ds value (or both) is/are not null. This can be achieved by changing the where clause, which should be:

...
where (connect_by_isleaf = 1) 
   or (level = 1 and (mc is not null or ds is not null))
...

edited Feb 10, 2021 at 22:16

answered Feb 9, 2021 at 14:24

user5683823

Sign up to request clarification or add additional context in comments.

3 Comments

stack user Over a year ago

Thank you. your query works wonderful. Sadly our requirements are evolving and was wondering if there is a feasibility to fetch sample2 output as well (I have edit my post above)

user5683823 Over a year ago

@stackuser - I believe it is possible, with a small modification. But I need to understand - you are "starting with" cp = 'A' (or whatever other value chosen by the user, I imagine). Then you want to walk the hierarchy, and show the "leaf" row in all cases, and in addition, show just the "root" row, as long as either mc or ds (or both) is/are not NULL. Is that it? If not, please clarify.

user5683823 Over a year ago

@stackuser - OK, I show how in an EDIT added at the bottom of my answer.

The Impaler · Accepted Answer · 2021-02-09 15:24:54Z

0

You can use a recursive CTE. For example:

with
n (cp, np, mc, ds, nnp, lvl) as (
  select cp, np, mc, ds, nnp, 1 from t where cp not in (
    select nnp from t where nnp is not null
  )
 union all
  select n.cp, n.np, n.mc, n.ds, t.nnp, n.lvl + 1
  from n
  join t on t.cp = n.nnp 
),
m as (select np, max(lvl) as max_lvl from n group by np)
select m.np, n.mc, n.ds
from n
join m on m.np = n.np and m.max_lvl = n.lvl

I didn't test this query for syntax issues, but the strategy is correct.

answered Feb 9, 2021 at 15:24

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

Oracle complex query with hierarchy

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related