Query to disply tree structures

[venur]

Hi,

I am trying to write a query which returns a list of recods in a tree structure.
When a user searches for TEXT then it has to display the entire trees for the searched text.

Table instance

INS_ID
TEXT

TABLE hierarchy

H_ID
INS_ID
PARENT_INS_ID
DISPLAY_ORDER

ex: data

1
/ \
1.2.1 1.2.2 ("Government")
/ \ / \
1.3.1 1.3.2 1.3.3 1.3.4 ("Government")


2
/ \
2.2.1 2.2.2 ("Government")
/ \ / \
2.3.1 2.3.2 2.3.3 2.3.4

and so on...

Presently I use some thing like this but results are different for every ins_id.

 SELECT DISTINCT LEVEL, text , ins_id , parent_ins_id, display_order              
           FROM instance ins, hierarchy h
          WHERE ins.ins_id = h.ins_id AND LEVEL > 1
     CONNECT BY PRIOR ins.ins_id = h.parent_ins_id
     START WITH ins.ins_id in (select ins_id from instance where text like '%Government%');

The result has to all trees which has the word "Government" in it one after the other.

Help is greatly appricated.

Thanks
venu

 

[SantaMufasa]

Venur,

What is there about Nodes 1 and 2 that "trigger" the tree walks? Is it the fact that Nodes 1 and 2 are "parents" of "children" whose "text" columns contain the word "Government"?...Or is the trigger another situation?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.