Home » SQL & PL/SQL » SQL & PL/SQL » Tricky Problem with Connect By
Tricky Problem with Connect By [message #302964] Wed, 27 February 2008 07:55 Go to next message
stevi2312
Messages: 3
Registered: February 2008
Junior Member
Hi,

I got the following problem:
I got a database like this:

Menu | Submenu | Function
---------------------
1 | | 100
1 | | 200
2 | 3 |
3 | 4 |
4 | | 300

You: A Menu can have either a submenu or a function.
A Submenu itself is again a menu, and can have a function or further submenus.

I´d like to know, with functions you can reach by specific menus, so in this case I´d like to have this result table:
Function 100 and 200 you can reach directly from Menu 1,

Function 300 you can reach by menu 2, 3, or 4.

Menu | Submenu | Function
---------------------
1 | | 100
1 | | 200
2 | | 300
3 | | 300
4 | | 300

I tried it with a "connect by prior", but i dont got the clue how to solve it correctly.

SELECT Menu, Submenu, Function FROM menutable CONNECT BY PRIOR sub_menu_id=menu_id;

Can someone help to solve this?

Thx in advance,
stevi2312
Re: Tricky Problem with Connect By [message #302975 is a reply to message #302964] Wed, 27 February 2008 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Welcome to the forum
2/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.
3/ Please read Not an EXPERT? Post in the NEWBIES forum, NOT here sticky.
4/ Always post your Oracle version (4 decimals).
5/ Post a test case: create table and insert statements.
Also post the result you want with these data.
6/ Copy and paste the execution of your tries.

Regards
Michel

Re: Tricky Problem with Connect By [message #303037 is a reply to message #302975] Wed, 27 February 2008 13:05 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member



SQL> select * from system.foo;

MENU SUBMENU FUNC
---------- ---------- ----------
1 100
1 200
2 3
3 4
4 300

SQL>


Quote:
select distinct menu,null,mf from (
select menu,
max(func) over (partition by nvl(grr,dbms_random.value(0,999999999999999999999))
order by lv desc ) mf
from (
select menu,func ,level lv ,
to_number(substr(ltrim(translate(
sys_connect_by_path(nvl2(submenu,menu,null),','),',_',' _')),1,1)) grr
from system.foo
connect by prior nvl(submenu,func)=menu
)
) order by menu
;







MENU N MF
---------- - ----------
1 200
1 100
2 300
3 300
4 300



Srini
Re: Tricky Problem with Connect By [message #303116 is a reply to message #303037] Thu, 28 February 2008 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please use [code] and [/code] tags.
Using a quote-tag implies you copied your code from somebody else. Using no tags around the query-results loses formatting, and doesn't show the data in columns correctly.
Re: Tricky Problem with Connect By [message #303141 is a reply to message #302964] Thu, 28 February 2008 02:24 Go to previous messageGo to next message
stevi2312
Messages: 3
Registered: February 2008
Junior Member
Thank you, I´m gonna try your solution!
Re: Tricky Problem with Connect By [message #303282 is a reply to message #302964] Thu, 28 February 2008 12:04 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Not sure about your oracle version. May want to look at this too.

select menu,connect_by_root func func
from foo
start with submenu is null
connect by prior menu=submenu ;

MENU FUNC
---------- ----------
1 100
1 200
4 300
3 300
2 300
Re: Tricky Problem with Connect By [message #303509 is a reply to message #303282] Fri, 29 February 2008 11:07 Go to previous messageGo to next message
stevi2312
Messages: 3
Registered: February 2008
Junior Member
Thank You!
My Oracle Version is 10g, so i can use "connect_by_root",
but maybe I have to use this script on a 9i database.

I think, than i´d have to use the "connect_by_path" operator, do I ?

[Updated on: Fri, 29 February 2008 11:07]

Report message to a moderator

Re: Tricky Problem with Connect By [message #303510 is a reply to message #303509] Fri, 29 February 2008 11:11 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, just take the first part of sys_connect_by_path to get the root.

Regards
Michel
Previous Topic: Display ratio of 2 numbers
Next Topic: Help with a procedure
Goto Forum:
  


Current Time: Thu Dec 08 02:29:56 CST 2016

Total time taken to generate the page: 0.17883 seconds