Home » SQL & PL/SQL » SQL & PL/SQL » connect by prior help (11G)
| connect by prior help [message #655069] |
Sat, 20 August 2016 20:37  |
 |
sanjuchand
Messages: 5
Registered: August 2016
|
Junior Member |
|
|
Hi,
I am trying to build a hierarchical query with a where condition. What I want is that if an attribute of a parent meets a condition, only then I want to drill down to children.
Can anyone provide any ideas?
select * from apps.fnd_menu_entries_vl fmv
where FMV.prompt is not null
start with menu_id ='95995'
CONNECT BY PRIOR sub_menu_id = menu_id;
|
|
|
|
|
|
| Re: connect by prior help [message #655071 is a reply to message #655069] |
Sat, 20 August 2016 23:05  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It would help to have create table and insert statements and what results that you want based on the sample data, something like what I have provided below, which is my best guess at what you are looking for without seeing those things. It just applies the where condition in an inline view (subquery in the from clause), prior to obtaining the hierarchy.
SCOTT@orcl_12.1.0.2.0> create table fnd_menu_entries_vl
2 (prompt varchar2(6),
3 menu_id varchar2(11),
4 sub_menu_id varchar2(11))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into fnd_menu_entries_vl values (null, '95995', '1')
3 into fnd_menu_entries_vl values (null, '1', '2')
4 into fnd_menu_entries_vl values ('p1', '95995', '1')
5 into fnd_menu_entries_vl values ('p2', '1', '2')
6 select * from dual
7 /
4 rows created.
SCOTT@orcl_12.1.0.2.0> select *
2 from (select *
3 from fnd_menu_entries_vl
4 where prompt is not null)
5 start with menu_id = '95995'
6 connect by prior sub_menu_id = menu_id
7 /
PROMPT MENU_ID SUB_MENU_ID
------ ----------- -----------
p1 95995 1
p2 1 2
2 rows selected.
|
|
|
|
| Re: connect by prior help [message #655073 is a reply to message #655071] |
Sun, 21 August 2016 01:31 |
 |
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote: if an attribute of a parent meets a condition, only then I want to drill down to children.
Does it mean:
1) I don't want to display a branch where the root ancestor meets a condition
2) I want to stop to display a branch as soon as the parent of the current row meets a condition
3) I want to stop to display a branch as soon as I encounter a condition
Barbara's query is stronger than requirements 1 and 2 and is equivalent to 3, it is:
4) Display the whole hierarchy but the rows that meet the condition and its descendants.
Modifying the test case in this way:
create table fnd_menu_entries_vl
(prompt varchar2(6),
menu_id varchar2(11),
sub_menu_id varchar2(11))
/
insert all
into fnd_menu_entries_vl values (null, '95995', '1')
into fnd_menu_entries_vl values (null, '1', '2')
into fnd_menu_entries_vl values ('p3', '1', '3')
into fnd_menu_entries_vl values ('p1', '99999', '1')
into fnd_menu_entries_vl values ('p2', '1', '2')
into fnd_menu_entries_vl values (null, '2', '3')
into fnd_menu_entries_vl values ('p4', '2', '4')
into fnd_menu_entries_vl values (null, '2', '5')
select * from dual
/
commit;
SQL> col path format a20
SQL> col root noprint
SQL> break on root skip 1
SQL> select connect_by_root menu_id root,
2 sys_connect_by_path(menu_id,'/') || '/' || sub_menu_id path,
3 prompt, menu_id, sub_menu_id
4 from fnd_menu_entries_vl
5 start with menu_id not in (select sub_menu_id from fnd_menu_entries_vl)
6 connect by prior sub_menu_id = menu_id
7 /
PATH PROMPT MENU_ID SUB_MENU_ID
-------------------- ------ ----------- -----------
/95995/1 95995 1
/95995/1/2 1 2
/95995/1/2/3 2 3
/95995/1/2/4 p4 2 4
/95995/1/2/5 2 5
/95995/1/2 p2 1 2
/95995/1/2/3 2 3
/95995/1/2/4 p4 2 4
/95995/1/2/5 2 5
/95995/1/3 p3 1 3
/99999/1 p1 99999 1
/99999/1/2 1 2
/99999/1/2/3 2 3
/99999/1/2/4 p4 2 4
/99999/1/2/5 2 5
/99999/1/2 p2 1 2
/99999/1/2/3 2 3
/99999/1/2/4 p4 2 4
/99999/1/2/5 2 5
/99999/1/3 p3 1 3
20 rows selected.
Barbara's query (modifying it to have the same kind of output):
SQL> select connect_by_root menu_id root,
2 sys_connect_by_path(menu_id,'/') || '/' || sub_menu_id path,
3 prompt, menu_id, sub_menu_id
4 from (select *
5 from fnd_menu_entries_vl
6 where prompt is not null)
7 start with menu_id not in (select sub_menu_id from fnd_menu_entries_vl)
8 connect by prior sub_menu_id = menu_id
9 /
PATH PROMPT MENU_ID SUB_MENU_ID
-------------------- ------ ----------- -----------
/99999/1 p1 99999 1
/99999/1/2 p2 1 2
/99999/1/2/4 p4 2 4
/99999/1/3 p3 1 3
4 rows selected.
Requirement 1):
SQL> select connect_by_root menu_id root,
2 sys_connect_by_path(menu_id,'/') || '/' || sub_menu_id path,
3 prompt, menu_id, sub_menu_id
4 from fnd_menu_entries_vl m
5 start with menu_id not in (select sub_menu_id from fnd_menu_entries_vl)
6 and prompt is not null
7 connect by prior sub_menu_id = menu_id
8 /
PATH PROMPT MENU_ID SUB_MENU_ID
-------------------- ------ ----------- -----------
/99999/1 p1 99999 1
/99999/1/2 1 2
/99999/1/2/3 2 3
/99999/1/2/4 p4 2 4
/99999/1/2/5 2 5
/99999/1/2 p2 1 2
/99999/1/2/3 2 3
/99999/1/2/4 p4 2 4
/99999/1/2/5 2 5
/99999/1/3 p3 1 3
10 rows selected.
Requirement 2):
SQL> select connect_by_root menu_id root,
2 sys_connect_by_path(menu_id,'/') || '/' || sub_menu_id path,
3 prompt, menu_id, sub_menu_id
4 from fnd_menu_entries_vl m
5 start with menu_id not in (select sub_menu_id from fnd_menu_entries_vl)
6 connect by prior sub_menu_id = menu_id and prior prompt is not null
7 /
PATH PROMPT MENU_ID SUB_MENU_ID
-------------------- ------ ----------- -----------
/95995/1 95995 1
/99999/1 p1 99999 1
/99999/1/2 p2 1 2
/99999/1/2/3 2 3
/99999/1/2/4 p4 2 4
/99999/1/2/5 2 5
/99999/1/2 1 2
/99999/1/3 p3 1 3
8 rows selected.
Requirement 3):
SQL> select connect_by_root menu_id root,
2 sys_connect_by_path(menu_id,'/') || '/' || sub_menu_id path,
3 prompt, menu_id, sub_menu_id
4 from fnd_menu_entries_vl m
5 start with menu_id not in (select sub_menu_id from fnd_menu_entries_vl)
6 and prompt is not null
7 connect by prior sub_menu_id = menu_id and prompt is not null
8 /
PATH PROMPT MENU_ID SUB_MENU_ID
-------------------- ------ ----------- -----------
/99999/1 p1 99999 1
/99999/1/2 p2 1 2
/99999/1/2/4 p4 2 4
/99999/1/3 p3 1 3
4 rows selected. |
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 05:17:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
