We have a table USER_HIERARCHY (USER_IS_MANAGER, USER)
I would like to now all hierarchical combinations for users :
suppose we have
(A,B) A is the manager of B
(A,C) A is the manager of C
(B,E) B is the manager of E
(F,A) F is the manager of A
I would like to get all the "hierarchical couples":
(user_is_manager, user)
A,B
F,B (because F is the manager of A and A is the manager of B)
A,C
F,C
B,E
A,E
F,E
F,A
I tried
select user_is_manager, user, level from user_hierarchy
connect by prior user_is_manager=user
But then I get only "direct" hierarchical relations. I do not get couples like F,B or F,C or A,E or F,E
Does anyone have an idea? Note, I cannot know the hierarchy depth by advance.
I would like to now all hierarchical combinations for users :
suppose we have
(A,B) A is the manager of B
(A,C) A is the manager of C
(B,E) B is the manager of E
(F,A) F is the manager of A
I would like to get all the "hierarchical couples":
(user_is_manager, user)
A,B
F,B (because F is the manager of A and A is the manager of B)
A,C
F,C
B,E
A,E
F,E
F,A
I tried
select user_is_manager, user, level from user_hierarchy
connect by prior user_is_manager=user
But then I get only "direct" hierarchical relations. I do not get couples like F,B or F,C or A,E or F,E
Does anyone have an idea? Note, I cannot know the hierarchy depth by advance.