Create custom Function or Stored Procedure

Question

I have a Hierarchy table with Master_id and Sub_id.

sub_id Master_id
2      1
3      2
4      1
5      3
6      7

I want to create an iterative function or stored procedure (I am not sure I never used any of them before) to create one more column which gives me the primary_master_Column (PMC)

sub_id Master_id PMC
2      1         1
3      2         1
4      1         1
5      3         1
6      7         7

Sorry I renamed the columns in my table. In my table 3 is a subsidiary of 2. but 2 in turn is a subsidiary of 1. So I want a separate column which would list the id which is on the top of the hierarchy. Hope am clear now — avg998877
– avg998877, Commented Feb 20, 2014 at 21:21

Egor Skriptunoff · Accepted Answer · 2014-02-20 21:31:44Z

2

select
  Master_id, sub_id, 
  max(PMC) keep(dense_rank first order by lev desc) as PMC
from
(
select 
  sub_id as PMC, level lev, 
  connect_by_root(Master_id) as Master_id,
  connect_by_root(sub_id) as sub_id
from your_table
connect by prior sub_id = Master_id
)
group by Master_id, sub_id

fiddle

answered Feb 20, 2014 at 21:31

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

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

Comments

MT0 · Accepted Answer · 2014-02-23 01:10:12Z

2

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test (sub_id, Master_id) AS
          SELECT 2,      1 FROM DUAL
UNION ALL SELECT 3,      2 FROM DUAL
UNION ALL SELECT 4,      1 FROM DUAL
UNION ALL SELECT 5,      3 FROM DUAL
UNION ALL SELECT 6,      7 FROM DUAL;

Query 1:

SELECT t.sub_id,
       t.master_id,
       CONNECT_BY_ROOT( t.master_id ) AS PMC
FROM   test t
       LEFT OUTER JOIN
       test x
       ON ( t.master_id = x.sub_id )
START WITH x.sub_id IS NULL
CONNECT BY PRIOR t.sub_id = t.master_id

Results:

| SUB_ID | MASTER_ID | PMC |
|--------|-----------|-----|
|      2 |         1 |   1 |
|      3 |         2 |   1 |
|      5 |         3 |   1 |
|      4 |         1 |   1 |
|      6 |         7 |   7 |

Query 2:

SELECT t.sub_id,
       t.master_id,
       CONNECT_BY_ROOT( t.master_id ) AS PMC
FROM   test t
START WITH NOT EXISTS ( SELECT 'x' FROM test x WHERE t.master_id = x.sub_id )
CONNECT BY PRIOR t.sub_id = t.master_id

Results:

| SUB_ID | MASTER_ID | PMC |
|--------|-----------|-----|
|      2 |         1 |   1 |
|      3 |         2 |   1 |
|      5 |         3 |   1 |
|      4 |         1 |   1 |
|      6 |         7 |   7 |

edited Feb 23, 2014 at 1:10

answered Feb 21, 2014 at 0:07

MT0

173k12 gold badges70 silver badges136 bronze badges

3 Comments

avg998877 Over a year ago

Thanks for your time on this.. I will try to get this to work using the CONNECT BY

avg998877 Over a year ago

Could you please explain why you were using PRIOR DBMS_RANDOM.VALUE IS NOT NULL condition??..thanks,AVG

MT0 Over a year ago

It is used to stop loops in the data - when I wrote the first draft of the query it was necessary to include it but I subsequently added in the START WITH statement and now the AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL is redundant and can be removed. I'll edit it out of the answer so as not to confuse.

Collectives™ on Stack Overflow

Create custom Function or Stored Procedure

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related