Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Query (10g windows)
Hierarchical Query [message #384143] Mon, 02 February 2009 05:46 Go to next message
TRaj
Messages: 82
Registered: September 2006
Member

Hi

I have a table (used for displaying menu)with 3 cols a (varchar2), b (varchar2) and c (char).

col a has desc, col b has menu levels like 0, 0.1, 0.1.1, 1, 1.1, 1.1.1 etc and c the node like P for parent and L for leaf.

Using sql query, how can we get the result set in a hierarchical order.

Thanks in advance.
Re: Hierarchical Query [message #384145 is a reply to message #384143] Mon, 02 February 2009 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unclear.
Post a Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Mon, 02 February 2009 05:48]

Report message to a moderator

Re: Hierarchical Query [message #384146 is a reply to message #384143] Mon, 02 February 2009 06:00 Go to previous messageGo to next message
TRaj
Messages: 82
Registered: September 2006
Member

Hi

The sample table and it's data.

CREATE TABLE menu
(
CD_DESC VARCHAR2(50) MNU_LEVEL VARCHAR2(10) MNU_TYPE char
)

cd_desc mnu_level mnu_type
A 0 P
a1 0.1 P
a11 0.1.1 L
a12 0.1.2 L
a2 0.2 P
a21 0.2.1 L
a22 0.2.2 L
a3 0.3 L

The output, I'm expecting is,
A 0
a1 0.1
a11 0.1.1
a12 0.1.2
a2 0.2
a21 0.2.1
a22 0.2.2
a3 0.3

Thanks.
Re: Hierarchical Query [message #384153 is a reply to message #384146] Mon, 02 February 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your output as explained in OraFAQ Forum Guide, "How to format your post?" section.
Use the "Preview Message" button to verify.

As you put it, the solution is simply
select cd_desc, mnu_level from menu;

Note that a test case requires insert statements.

Regards
Michel
Re: Hierarchical Query [message #384156 is a reply to message #384146] Mon, 02 February 2009 06:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're going to post SQL, please post VALID sql.

The problem you have posted is a trivial problem.
So trivial in fact that I'm sure that you need to go away and produce some data that shows what you actually want.

CREATE TABLE menu 
(CD_DESC   VARCHAR2(50)
,MNU_LEVEL VARCHAR2(10)
,MNU_TYPE char(1) );
 
 insert into menu values ('A'   ,'0', 'P');
 insert into menu values ('a1'  ,'0.1', 'P');
 insert into menu values ('a11' ,'0.1.1', 'L');
 insert into menu values ('a12' ,'0.1.2', 'L');
 insert into menu values ('a2'  ,'0.2', 'P');
 insert into menu values ('a21' ,'0.2.1', 'L');
 insert into menu values ('a22' ,'0.2.2', 'L');
 insert into menu values ('a3'  ,'0.3', 'L');
 
 select cd_desc,mnu_level
 from   menu
 order  by cd_desc;

CD_DESC                                            MNU_LEVEL
-------------------------------------------------- ----------
A                                                  0
a1                                                 0.1
a11                                                0.1.1
a12                                                0.1.2
a2                                                 0.2
a21                                                0.2.1
a22                                                0.2.2
a3                                                 0.3


[Drat - beaten to it]

[Updated on: Mon, 02 February 2009 06:38]

Report message to a moderator

Previous Topic: File operation(UTL_FILE)
Next Topic: Bulk insert count limit
Goto Forum:
  


Current Time: Fri Dec 02 12:41:49 CST 2016

Total time taken to generate the page: 0.14721 seconds