Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical View of Query
Hierarchical View of Query [message #192706] Wed, 13 September 2006 05:46 Go to next message
ramusoft
Messages: 8
Registered: April 2005
Location: Karur, Tamilnadu, India
Junior Member

I have 3 talbes,
WORKQUEUE,
REQUESTDETAIL and
PACKAGEOBJECT
and I Passed the below Query in SQL,

"SELECT W.REQUESTID,
RD.PEPID,RD.POLICYPACKAGEID,POB.PACKAGEDESCRIPTION
FROM WORKQUEUE W,REQUESTDETAIL RD,PACKAGEOBJECT POB
Where
W.REQUESTID=RD.REQUESTID
AND RD.POLICYPACKAGEID = POB.POLICYPACKAGEID"

Which returns rows as follows:


REQUESTID PEPID POLICYPACKAGEID
---------- ------ ---------------
PACKAGEDESCRIPTION
------------------------------
10 P10 MEDP_PACK
SECURITY

10 P1 MEDP_PACK
SECURITY

12 P12 MEDP_PACK12
ANTI-VIRUS

3 rows selected.
But I need the output as follows:
10
P10 MEDP_PACK SECURITY
P1 MEDP_PACK SECURITY
20
P12 MEDP_PACK12 ANTI-VIRUS

As a Hierarchical Structure.

Can anyone Help Me?
Re: Hierarchical View of Query [message #192716 is a reply to message #192706] Wed, 13 September 2006 06:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why?
Is this just a display problem, in that you'd like to show the id once, followed by the details for that id, or have you actually got a piece of code somewhere that needs the data in this format.

If it is just a display problem then look at the SQL*Plus BREAK command
Re: Hierarchical View of Query [message #192752 is a reply to message #192716] Wed, 13 September 2006 07:57 Go to previous messageGo to next message
ramusoft
Messages: 8
Registered: April 2005
Location: Karur, Tamilnadu, India
Junior Member

Yes, I want the the data to be shown as the id once, followed by the details for that id

10
----P10 MEDP_PACK SECURITY
----P1 MEDP_PACK SECURITY
12
----P12 MEDP_PACK12 ANTI-VIRUS

Something output like Connect by Prior command.
Re: Hierarchical View of Query [message #192774 is a reply to message #192752] Wed, 13 September 2006 09:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you definitely want it on a different line, the first solution that comes to mind is this.

create table temp_break (col_1  number, col_2 varchar2(200));

insert into temp_break values(10,'line 1');
insert into temp_break values(10,'line 2');
insert into temp_break values(10,'line 3 with some text');
insert into temp_break values(20,'Another line 1');
insert into temp_break values(20,'Line 2, again');

SQL> break on col_1
SQL> select distinct col_1,null col_2 from temp_break
  2  union all
  3  select col_1,col_2 from temp_break
  4  order by col_1,col_2 nulls first;

     COL_1 COL_2
---------- -----------------------------------------------
        10
           line 1
           line 2
           line 3 with some text
        20
           Another line 1
           Line 2, again
Re: Hierarchical View of Query [message #192856 is a reply to message #192774] Wed, 13 September 2006 23:33 Go to previous message
ramusoft
Messages: 8
Registered: April 2005
Location: Karur, Tamilnadu, India
Junior Member

Dear,

Its Working fine. Thanks For your timing help.

With Regards,
Ramki
Previous Topic: Automating adding partitions by using data dictionary
Next Topic: Error when connecting to Oracle database
Goto Forum:
  


Current Time: Sat Dec 03 13:48:41 CST 2016

Total time taken to generate the page: 0.08678 seconds