Home » SQL & PL/SQL » SQL & PL/SQL » connect by prior help (11G)
connect by prior help [message #655069] Sat, 20 August 2016 20:37 Go to next message
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 #655070 is a reply to message #655069] Sat, 20 August 2016 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 25963
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: connect by prior help [message #655071 is a reply to message #655069] Sat, 20 August 2016 23:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8827
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65458
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;
The whole hierarchy is (adding a "path" column and a "root" column which is not printed use only to skip 1 line before each main branch):
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.
Re: connect by prior help [message #655080 is a reply to message #655073] Sun, 21 August 2016 11:00 Go to previous message
sanjuchand
Messages: 5
Registered: August 2016
Junior Member
Awesome guys..thank you so much for the help..so simple and elegant..you ROCK!!!
Previous Topic: Dropping tables in a stored procedure
Next Topic: How to remove duplicate value from type in SQL
Goto Forum:
  


Current Time: Wed Apr 25 01:48:52 CDT 2018

Total time taken to generate the page: 0.01637 seconds