Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical query exclusions
Hierarchical query exclusions [message #294875] Sun, 20 January 2008 06:07 Go to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Hi there,
I'm trying to create a view that looks at responsibilities and returns the menu as well as all sub-menu's. I'm using the CONNECT BY feature of oracle to recurse through the sub-levels. What I would like to know is if there is a way to not recurse when a certain condition is met. There is a column called prompt and when that is NULL I do not want to travel down that tree.

SELECT fr.responsibility_id, fm.menu_name, menu.*
FROM   (SELECT connect_by_root fmet.menu_id top_menu_id,
							 fmet.menu_id menu_id,
							 fmet.sub_menu_id,
							 fmet.function_id,
							 fmet.prompt
				FROM   fnd_menu_entries_vl fmet
				CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id) menu,
			 fnd_responsibility fr,
			 fnd_menus fm
WHERE  fr.menu_id = menu.top_menu_id
AND    menu.prompt IS NOT NULL
AND    menu.function_id IS NOT NULL
AND    fm.menu_id = menu.menu_id
AND    fr.responsibility_id = 50853
AND    menu.menu_id NOT IN
			 (SELECT pem.menu_id
				 FROM   pls_excluded_menu_v pem
				 WHERE  pem.responsibility_id = fr.responsibility_id)
AND    menu.function_id NOT IN
			 (SELECT frf.action_id
				 FROM   fnd_resp_functions frf
				 WHERE  frf.responsibility_id = fr.responsibility_id)
Re: Hierarchical query exclusions [message #294877 is a reply to message #294875] Sun, 20 January 2008 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to stop the recursive search in a hierarchy put the condition in the CONNECT BY clause.

Regards
Michel
Re: Hierarchical query exclusions [message #295295 is a reply to message #294875] Tue, 22 January 2008 01:04 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Here's the working view for anyone interested, thanks for the help Michael:

SELECT fr.responsibility_id,
			 fr.responsibility_key,
			 fm.menu_name,
			 menu.top_menu_id,
			 menu.menu_id menu_id,
			 menu.sub_menu_id,
			 menu.function_id,
			 menu.prompt
FROM   (SELECT connect_by_root fmet.menu_id top_menu_id,
							 fmet.menu_id menu_id,
							 fmet.sub_menu_id,
							 fmet.function_id,
							 fmet.prompt
				FROM   fnd_menu_entries_vl fmet
				CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id
						AND    PRIOR fmet.prompt IS NOT NULL) menu,
			 fnd_responsibility fr,
			 fnd_menus fm
WHERE  fr.menu_id = menu.top_menu_id
AND    menu.function_id IS NOT NULL
AND    menu.prompt IS NOT NULL
AND    fm.menu_id = menu.menu_id
AND    nvl(fr.end_date, SYSDATE) < SYSDATE + 1
--AND    fr.responsibility_key LIKE 'PLS%'
AND    menu.menu_id NOT IN
			 (SELECT pem.menu_id
				 FROM   pls_excluded_menu_v pem
				 WHERE  pem.responsibility_id = fr.responsibility_id)
AND    menu.function_id NOT IN
			 (SELECT frf.action_id
				 FROM   fnd_resp_functions frf
				 WHERE  frf.responsibility_id = fr.responsibility_id)
Re: Hierarchical query exclusions [message #295298 is a reply to message #295295] Tue, 22 January 2008 01:17 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: can i able to view the rows recently inserted?
Next Topic: How To Create Dynamic Query
Goto Forum:
  


Current Time: Fri Dec 02 23:19:29 CST 2016

Total time taken to generate the page: 0.04688 seconds