| Another Hierarchical circular reference question [message #617779] |
Thu, 03 July 2014 09:50  |
 |
lott42_gmail
Messages: 146
Registered: June 2010
|
Senior Member |
|
|
I want to display only circular reference problems depending on who is logged in.
data:
with data as
(select 'a' emp_id, 'c' supv_id
from dual
union all
select 'b' emp_id, 'a' supv_id
from dual
union all
select 'c' emp_id, 'b' supv_id
from dual
union all
select 'e' emp_id, 'g' supv_id
from dual
union all
select 'f' emp_id, 'h' supv_id
from dual
union all
select 'g' emp_id, 'e' supv_id
from dual
union all
select 'h' emp_id, '' supv_id from dual)
select d.*, CONNECT_BY_ISCYCLE
from data d
WHERE connect_by_iscycle > 0
CONNECT BY nocycle PRIOR emp_id = supv_id
with this data the results are
EMP_ID SUPV_ID CONNECT_BY_ISCYCLE
a c 1
b a 1
c b 1
e g 1
g e 1
So if user a,b,c logs in, I want results
EMP_ID SUPV_ID CONNECT_BY_ISCYCLE
a c 1
b a 1
c b 1
to come back. and like wise if user e logs in, I want result(s)
EMP_ID SUPV_ID CONNECT_BY_ISCYCLE
e g 1
I tried something like this if login user was 'a'
with data as
(select 'a' emp_id, 'c' supv_id
from dual
union all
select 'b' emp_id, 'a' supv_id
from dual
union all
select 'c' emp_id, 'b' supv_id
from dual
union all
select 'e' emp_id, 'g' supv_id
from dual
union all
select 'f' emp_id, 'h' supv_id
from dual
union all
select 'g' emp_id, 'e' supv_id
from dual
union all
select 'h' emp_id, '' supv_id from dual)
select *
from (select d.*, CONNECT_BY_ISCYCLE
from data d
WHERE connect_by_iscycle > 0
CONNECT BY nocycle PRIOR emp_id = supv_id) t
where emp_id = 'a'
or supv_id = 'a'
Results
EMP_ID SUPV_ID CONNECT_BY_ISCYCLE
a c 1
b a 1
Close but NOT exactly what I wanted 
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Another Hierarchical circular reference question [message #617790 is a reply to message #617787] |
Thu, 03 July 2014 12:02  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> def us=a
SQL> with data as
2 (select 'a' emp_id, 'c' supv_id
3 from dual
4 union all
5 select 'b' emp_id, 'a' supv_id
6 from dual
7 union all
8 select 'c' emp_id, 'b' supv_id
9 from dual
10 union all
11 select 'e' emp_id, 'g' supv_id
12 from dual
13 union all
14 select 'f' emp_id, 'h' supv_id
15 from dual
16 union all
17 select 'g' emp_id, 'e' supv_id
18 from dual
19 union all
20 select 'h' emp_id, '' supv_id from dual)
21 select d.emp_id, d.supv_id
22 from ( select d.*, sys_connect_by_path(emp_id,',')||',' p
23 from data d
24 where connect_by_iscycle = 1
25 CONNECT BY nocycle PRIOR emp_id = supv_id ) d
26 where d.p like '%,&us.,%'
27 /
E S
- -
a c
b a
c b
3 rows selected.
SQL> def us=b
SQL> /
E S
- -
a c
b a
c b
3 rows selected.
SQL> def us=c
SQL> /
E S
- -
a c
b a
c b
3 rows selected.
SQL> def us=e
SQL> /
E S
- -
e g
g e
2 rows selected.
SQL> def us=h
SQL> /
no rows selected
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
