I have this table:
Desired result:
What i'm trying:
select SEG from my_table CONNECT BY PRIOR PARENT_SEG_ID = SEG_ID;
So, in the column LEVEL_LOCATOR we can see f.e. that the LEVEL 5.9 has 6 childs, and 5.9.4 has 2 childs. And, f.e., the parent of the child 5.9.1 is Postpaid that has the SEG_ID = 361978 and it's in the row of this child as PARENT_SEG_ID. So parent and child connect with SEG_ID and PARENT_SEG_ID.
I'm trying to do this but so far i can't have the result i want.
Note: The col_a is the concatenation SEG column in the following format: parent - child - child
(if it has childs)
I think you need to walk the tree the other way around, and can then use sys_connect_by_path()
to concatenate the segment names, and connect_by_isleaf
to only show the leaf nodes:
select col_a, col_b
from (
select ltrim(sys_connect_by_path(seg, ' - '), ' - ') as col_a,
seg_id as col_b,
connect_by_isleaf as isleaf
from my_table
start with seg = 'Prepaid'
connect by parent_seg_id = prior seg_id
)
where isleaf = 1;
COL_A COL_B
-------------------------------------------------- ----------
Prepaid 361977
Prepaid - Voice Mobile 2687522995
Prepaid - Internet Mobile 2687525564
Prepaid - Data Mobile 2687527808
Prepaid - Voice Fixa - Default 2687531979
Prepaid - Voice Fixa - Internal PABX 2687534794
The ltrim()
is there to remove an extra -
that the path includes at the start.
But you need a starting condition; I've gone with 'Prepaid', which includes an extra row in the output, but you could start with seg_id = 361978
instead, or some other condition depending on what data you actually want to see.
You could also using consider recursive subquery factoring.