Below is my scenario and any help in this regard would be highly appreciated.
I need to get the list of Managers and reporting persons to that manager with LEVEL at ID level.
But the Fact table doesnt have hierarchial relationship with itself. Needs to get the id's from key's from 2 different dimension tables to generate the list.
I am not familiar with CONNECT_BY and JOINS to get list.
Fact table:
Person_ky Mngr_ky
100 3
50 6
2000 9999
60 3
150 4
20 2
In DIM Person table
Person_ky Pers_Id
100 x
50 y
2000 aaaa
60 a
150 m
20 q
In DIM Manger table
Mngr_ky Mngr_id
3 m
6 a
4 q
2 t
999 bbb
In the above scenario,
pers_ky 100 (Id -'x')
is reporting to mngr_ky 3(Mngr_id - 'm')
and person_ky 150 ('m')
is reporting to mngr_ky 4 ('q')
and person_ky 150 ('q')
reporting to mngr_ky 2('t')
..
So Need to have for person x
, his managers are 'm','q' and 't' in the result.
pers_id mgr_id level
x m 1 --> for pers_ky 100
x q 2
x t 3
y a 1 --> for per_ky 60
y m 2
y q 3
y t 4
aaaa bbb 1 --> for pers_ky 2000
a m 1 --> for pers_ky 60
a q 2
.
.
Best Answer
This appears to be a straightforward connect by where you're getting the next person based on the prior manager. The only difference being you want to get the top-level (root) person listed for each row it applies to.
This can easily be found using the
connect_by_root
function (docs), which will return you the value of the column listed at the root node in each hierarchy.This gives you a query like:
For a full example, see this fiddle.