Hierarchical Query

[kloner]

Hi all,

I am trying to write my first hierarchical query in oracle 8i. The data I have is as follows :

WID Name ParentID
1 Telecomunications NULL
2 Mobiles 1
3 Handheld Phones 8
4 Foxtel 1
5 Wholesale NULL
6 ISP Connect 5
7 HyperConnect 8
8 GOC NULL
9 Faults 8
10 Maintenance 1
11 Provisions 8
12 Banking NULL

In my recordset query i would like the data to display as follows (WID, NAME, PARENTID):

Banking

GOC
Faults
Maintenance
Provisions

Telecomunications
Foxtel
Handheld Phones
Mobiles

Wholesale
HyperConnect
ISP Connect

Can anyone help me here? There are only 2 levels allowed. I have been looking at a few pots and have gotten this far :

SELECT RPAD( ' ', LEVEL * 2 ) || NAME AS NAME, PARENTID, WORKGROUPID FROM SEC_WORKGROUP
CONNECT BY PRIOR WORKGROUPID = PARENTID START WITH PARENTID IS NULL


How do I order the second level by name?

kloner

 

[kloner]

Anyone able to help me out here? kloner

 

[sem]

You can not change the order of hierarchical query. If you need only 2 levels, I'd suggest you to use explicit self join. Regards, Dima

 

[jee]

Actually there are some methods to get the results you want with a hierarchical query. It just takes a little more work. Here is a link that explains 3 different methods:

http://www.jlcomp.demon.co.uk/faq/connectby.html

However, like KLONER says, if you really only have 2 levels (and will always only have two levels), you can get the result easier by simply joining the table to itself