Home » SQL & PL/SQL » SQL & PL/SQL » getting table values as a hierarchy (merged)
getting table values as a hierarchy (merged) [message #327619] Tue, 17 June 2008 01:20 Go to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
i have a table containing parent_id and child_id. how to get the hierarchy form of this table without using connect by prior.
Re: getting table values as a hierarchy [message #327620 is a reply to message #327619] Tue, 17 June 2008 01:21 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Search connect By Prior clause in sql
Re: getting table values as a hierarchy [message #327621 is a reply to message #327620] Tue, 17 June 2008 01:22 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sorry i haven't seen the full text.

But why you wan't it without connect by prior.
Re: getting table values as a hierarchy [message #327625 is a reply to message #327621] Tue, 17 June 2008 01:34 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
am not supposed to use connect by prior. is there any other way of gettin the hierarchy with out using connect by prior?
Re: getting table values as a hierarchy [message #327626 is a reply to message #327625] Tue, 17 June 2008 01:38 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I think No??

Regards,
Rajat Ratewal

Re: getting table values as a hierarchy [message #327629 is a reply to message #327619] Tue, 17 June 2008 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't get it with basic SQL as this one only knows sets and not hierarchies.
Every RDBMS has introduced its extension to handle this, Oracle with CONNECT BY.

The only other way is to create a PL/SQL procedure that does it.

Regards
Michel

[Updated on: Tue, 17 June 2008 01:56]

Report message to a moderator

Re: getting table values as a hierarchy [message #327637 is a reply to message #327629] Tue, 17 June 2008 02:12 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
i have achieved this by using the connect by prior as follows

select parent_id,child_id,level from <tablename> connect by prior child_id=parent_id
start with parent_id=-1

can someone tel how to achieve the same result using PL/SQL. if possible pl explain with coding.
Re: getting table values as a hierarchy [message #327647 is a reply to message #327637] Tue, 17 June 2008 02:34 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
krithi86 can you clarify why you need it to do with PL/SQL.
When you can achieve this in one SQL Statement.

Why you wan't to re-invent the wheel??

Regards,
Rajat
Re: getting table values as a hierarchy [message #327649 is a reply to message #327637] Tue, 17 June 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
can someone tel how to achieve the same result using PL/SQL. if possible pl explain with coding.

Just think about it works and simulate it.
Not specific to PL/SQL, it is the same thing in any language, it is just algorithm.

Regards
Michel
Re: getting table values as a hierarchy [message #327655 is a reply to message #327647] Tue, 17 June 2008 02:43 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
actually am working on PL/SQL, this task is given in order to find a solution to achieve this using PL/SQL. am not supposed to use SQL coding. i know the direct way. but the challenge is doin it in the indirect way.
Re: getting table values as a hierarchy [message #327677 is a reply to message #327655] Tue, 17 June 2008 03:54 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
http://www.orafaq.com/forum/t/29215/2/

Go through the link it will give you some idea how connect by
prior works in oracle.Then you can create it's functionality in PL/SQL.

Once you are able to understand the algorithm then you can easily implement it.

Regards,
Rajat Ratewal
Re: getting table values as a hierarchy [message #327679 is a reply to message #327677] Tue, 17 June 2008 04:08 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
cn we achieve d same using joins? if yes please explain more.
Re: getting table values as a hierarchy [message #327681 is a reply to message #327679] Tue, 17 June 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No.

Regards
Michel
using self join [message #327828 is a reply to message #327619] Wed, 18 June 2008 02:38 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
i have a table as follows.

parent child name
-1 1 A1
1 2 A2
1 3 A3
1 4 A4
1 5 A5

how to obtain parent-child relationship using self join. i know this can be easily done by "connet by" but how to achieve this using self join?
Re: using self join [message #327835 is a reply to message #327828] Wed, 18 June 2008 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you want the whole hierarchy you can't do it WITHOUT connect by unless you use PL/SQL.

Before your next post, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: using self join [message #327836 is a reply to message #327828] Wed, 18 June 2008 02:47 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In addition, the answer does not change since your previous post yesterday: getting table values as a hierarchy

Regards
Michel

Previous Topic: using select statement
Next Topic: diff between rollup and cube
Goto Forum:
  


Current Time: Thu Feb 06 18:45:51 CST 2025