Query help tree structure

[sonaliak]

WORK_ID WORK_ITEM_CO WORK_LEVEL WORK_PAR4 WORK_PAR5 WORK_PAR6 WORK_DESCRIPTION
---------- ------------ ---------- ---------- ---------- ---------- -------------------------------
9 ES3000 4 ES3000, System Maintenance
44 Ch1479 5 9 Level 5 desc
45 Ch1479 5 9 Level 5 desc
1132 Ch1479 6 9 1479 Level 6 desc
1509 Ch1132 7 9 1479 1132 Level 7 desc
1479 Ch1479 5 9 Level 5 desc
1480 Ch1479 6 9 1479 Level 6 desc
1481 Ch1480 7 9 1479 1480 Level 7 desc
1483 Ch1132 7 9 1479 1132 Level 7 desc
1523 Ch1132 7 9 1479 1132 Level 7 desc

I want to write a query which will put this in tree structure

like
WORK_ID WORK_ITEM_CO WORK_LEVEL WORK_PAR4 WORK_PAR5 WORK_PAR6 WORK_DESCRIPTION
---------- ------------ ---------- ---------- ---------- ---------- -------------------

9 ES3000 4 ES3000, System Maintenance
44 Ch1479 5 9 Level 5 desc
45 Ch1479 5 9 Level 5 desc

1479 Ch1479 5 9 Level 5 desc

1132 Ch1479 6 9 1479 Level 6 desc
1509 Ch1132 7 9 1479 1132 Level 7 desc
1483 Ch1132 7 9 1479 1132 Level 7 desc
1523 Ch1132 7 9 1479 1132 Level 7 desc

1480 Ch1479 6 9 1479 Level 6 desc
1481 Ch1480 7 9 1479 1480 Level 7 desc

So in the above tree -

work_id 9 is at work_level 4 and has 9 children
then work_id 44 and 45 at work_level =5 has no children
then work_id 1479 at work_level =5 has 6 children ( work_id =1132, 1509,1483,1523,1480, 1481)
then work_id 1132 has 3 children ( work_id = 1509, 1483, 1523)
then work_id 1480 has one child work_id =1481

I tried to write a query like this but it doesn't seem to do it correct, any help please !!

SELECT
RPAD ( ' ', 3 * ( LEVEL - 1 ), ' ' )|| work_id||
WORK_ITEM_CODE || ': ' || WORK_DESCRIPTION , level, work_level
FROM
Work Where work_par4 =9
START WITH
WORK_level =4
CONNECT BY
PRIOR WORK_id =work_par4 ;

This is what I am getting with the query I can get only one LEVEL (2) here, how do I get to the multiple levels-

RPAD('',3*(LEVEL-1),'')||WORK_ID||WORK_ITEM_CODE||':'||WORK_DESCRIPTIon LEVEL WORK_LEVEL
--------- ----------
44 Ch1479: Level 5 desc 2 5

45 Ch1479: Level 5 desc 2 5

1132 Ch1479: Level 6 desc 2 6

1509 Ch1132: Level 7 desc 2 7

1479 Ch1479: Level 5 desc 2 5

1480 Ch1479: Level 6 desc 2 6

1481 Ch1480: Level 7 desc 2 7

1483 Ch1132: Level 7 desc 2 7

1523 Ch1132: Level 7 desc 2 7


9 rows selected.

Instead of
9 Project level
44 Ch1479: Level 5 desc 2 5

45 Ch1479: Level 5 desc 2 5

1132 Ch1479: Level 6 desc 2 6
1509 Ch1132: Level 7 desc 2 7
1483 Ch1132: Level 7 desc 2 7
1523 Ch1132: Level 7 desc 2 7

1480 Ch1479: Level 6 desc 2 6
1481 Ch1480: Level 7 desc 2 7


Work_id is the primary key on this table.

Thanks for help
Sonali

[sonaliak]

I have created a view to make it simple still it doesn't work... says I cannot have JOIN and connect by........

Create or replace view workParent
as
Select work_id, work_item_code,
Case when work_par6 is null and work_par5 is null then work_par4
when work_par6 is null and work_par5 is not null then work_par5
when work_par6 is not null and work_par5 is not null then work_par6
end as parent_id , Work_level, work_description
from mwebwork where work_par4 = 9 ;


Create or replace view workParentChild
as
Select w.work_id, w.work_item_code, p.parent_id,
Case when w.work_par6 is null and w.work_par5 is null and w.work_par4 = p.parent_id then w.work_id
when w.work_par6 is null and w.work_par5 is not null and w.work_par5 = p.parent_id then w.work_id
when w.work_par6 is not null and w.work_par5 is not null and w.work_par6 = p.parent_id then w.work_id
end as child_id , w.Work_level, w.work_description
from mwebwork w, workParent p
where w.work_par4 = 9
and w.work_id = p.work_id;
WORK_ID WORK_ITEM_CO PARENT_ID CHILD_ID WORK_LEVEL WORK_DESCRIPTION
---------- ------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
44 Ch1479 9 44 5 Level 5 desc
45 Ch1479 9 45 5 Level 5 desc
1132 Ch1479 1479 1132 6 Level 6 desc
1509 Ch1132 1132 1509 7 Level 7 desc
1479 Ch1479 9 1479 5 Level 5 desc
1480 Ch1479 1479 1480 6 Level 6 desc
1481 Ch1480 1480 1481 7 Level 7 desc
1483 Ch1132 1132 1483 7 Level 7 desc
1523 Ch1132 1132 1523 7 Level 7 desc
9 rows selected.



SELECT
RPAD ( ' ', 3 * ( LEVEL - 1 ), ' ' )||
WORK_ITEM_CODE || ': ' || WORK_DESCRIPTION , level, work_level, work_id
FROM
workParentChild
START WITH
LEVEL =1
CONNECT BY
PRIOR child_id =parent_id ;
thanks
Sonali

[Edited by sonaliak on 12-05-2001 at 12:49 PM]