Home » SQL & PL/SQL » SQL & PL/SQL » problem with heirarical tree level (oracle 10 g ,xp)
problem with heirarical tree level [message #438191] Thu, 07 January 2010 07:57 Go to next message
kamar_19
Messages: 52
Registered: June 2005
Location: pakistan
Member
hi,

i m using following heirarical query but i receieve different level value for the same parent.

query is

SELECT DISTINCT BOQ_SERIAL_NO,level,PARENT_NO

FROM PRJ_QUOTATION_BOQ_LINES
WHERE PRI_SUB_ID = 3

START WITH BOQ_SERIAL_NO=10
CONNECT BY PRIOR BOQ_SERIAL_NO= PARENT_NO;


and its out put is


BOQ_SERIAL_NO,LEVEL,PARENT_NO
20,2,10
30,2,10
40,2,10
120,2,10
130,2,10
140,2,10
160,2,10
190,2,10
310,2,10
360,2,10
520,2,10
570,2,10
580,2,10
590,2,10
600,2,10
610,2,10
620,2,10
930,2,10
940,2,10
950,2,10
960,2,10
970,2,10
980,2,10
990,2,10
1000,2,10
1010,2,10
1020,2,10
50,3,40
60,3,40
70,3,40
80,3,40
90,3,40
100,3,40
110,3,40
150,3,140
170,3,160
180,3,160
170,4,160
180,4,160

200,3,190
210,3,190
220,3,190
230,3,190
240,3,190
250,3,190
260,3,190
270,3,190
280,3,190
290,3,190
300,3,190
200,4,190

210,4,190
220,4,190
230,4,190
240,4,190

please guide me in this context

thanx n regards

Qamar Abbas.
Re: problem with heirarical tree level [message #438202 is a reply to message #438191] Thu, 07 January 2010 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a workingTest case: create table and insert statements along with the result you want with these data.

Also explain what is your problem. Putting a couple of lines in bold is not sufficient to understand what it is.

Regards
Michel
Re: problem with heirarical tree level [message #438204 is a reply to message #438191] Thu, 07 January 2010 08:42 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
You probably have two ways in your tree, like in my simple example:
1->10->15->20->30   level=4
1->10    ->20->30   level=3

WITH PRJ_QUOTATION_BOQ_LINES AS 
(
  SELECT 10 BOQ_SERIAL_NO ,  0 PARENT_NO, 3 PRI_SUB_ID FROM dual
    UNION ALL
  SELECT 15 BOQ_SERIAL_NO , 10 PARENT_NO, 3 PRI_SUB_ID FROM dual    
    UNION ALL
  SELECT 20 BOQ_SERIAL_NO , 10 PARENT_NO, 3 PRI_SUB_ID FROM dual    
    UNION ALL
  SELECT 20 BOQ_SERIAL_NO , 15 PARENT_NO, 3 PRI_SUB_ID FROM dual    
    UNION ALL
  SELECT 30 BOQ_SERIAL_NO , 20 PARENT_NO, 3 PRI_SUB_ID FROM dual    
    UNION ALL
  SELECT 40 BOQ_SERIAL_NO , 20 PARENT_NO, 3 PRI_SUB_ID FROM dual    
  )
 SELECT DISTINCT BOQ_SERIAL_NO,level,PARENT_NO
            FROM PRJ_QUOTATION_BOQ_LINES
           WHERE PRI_SUB_ID = 3
      START WITH BOQ_SERIAL_NO=10
CONNECT BY PRIOR BOQ_SERIAL_NO= PARENT_NO
        ORDER BY level;

BOQ_SERIAL_NO	LEVEL	PARENT_NO
---------------------------------------
10	        1	0
15	        2	10
20	        2	10
20	        3	15
30	        3	20
40	        3	20
30	        4	20
40	        4	20

[Updated on: Thu, 07 January 2010 08:44]

Report message to a moderator

Previous Topic: Between dates sql
Next Topic: How to identify the locks in oracle db objects? i dont have access to check the v$ objects..
Goto Forum:
  


Current Time: Mon Sep 26 19:42:37 CDT 2016

Total time taken to generate the page: 0.07771 seconds