Home » SQL & PL/SQL » SQL & PL/SQL » connect by prior, how to get all the level even started from middle
connect by prior, how to get all the level even started from middle [message #205218] Thu, 23 November 2006 23:08 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Dear All,

I've one table, the structure and possible data is mentioned under.

HD VARCHAR2(Cool
HD1 VARCHAR2(Cool
HD_LEVEL NUMBER(2)

HD HD1 HD_LEVEL
00031200 10000000 1
10000000 19000000 5
10010000 10000000 7
10020000 10000000 7
19000000 98000000 9
98000000 NULL 11 --> LAST

what i want is a sql, that if we pass any HD, it will provide me
lower level as well as upper level node data, like if I've give input as 00031200, for this if i run this query :-

SELECT hd, hd1, hd_level FROM hddata CONNECT BY PRIOR hd1=hd START WITH hd='00031200';

then it will show the output like this, which is correct one

00031200 10000000
10000000 19000000
19000000 98000000
98000000 NULL

and if I've given input as 10000000 then it starts from 10000000 and go to the lower hierarchy, it will not give above node .. this is the limitation of the query which I write,

now is thr is any way to get the upper level nodes by using connect by or some other clause .. i would like to tell you that table has over lakhs of records and the hierarchy is not in any sequence.


Can u help me please..

Thanks & Regards
Amit
Re: connect by prior, how to get all the level even started from middle [message #205220 is a reply to message #205218] Thu, 23 November 2006 23:33 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
One more thing, i would like to mention,

I'm using oracle 9i, user is providing a HD, based on that I've to found all the upper level and lower level HD's, I think in that case I cannot ignore Start with Clause. If it is possible through any PL/SQL program/procedure then kindly help me.
Re: connect by prior, how to get all the level even started from middle [message #205237 is a reply to message #205220] Fri, 24 November 2006 00:48 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You would have to perform a UNION ALL, with one part searching down and the other part searching up.

SELECT hd, hd1, hd_level 
FROM hddata 
CONNECT BY PRIOR hd1=hd 
START WITH hd1='10000000'
UNION ALL
SELECT hd, hd1, hd_level 
FROM hddata 
CONNECT BY PRIOR hd=hd1 
START WITH hd='10000000'


Ross Leishman
Previous Topic: Solved: Merge Error
Next Topic: Getting the UNIX file size from PLSQL
Goto Forum:
  


Current Time: Sun Dec 04 12:34:53 CST 2016

Total time taken to generate the page: 0.09821 seconds