Hierarchical Query with search parameters (Pruning)

The following is a query from a friend, Reproducing it here:

I have a menu structure using a hierarchical query. i have search field to enter some string and search.
I should get the result in the same menu format with with the node and its parents.
Can you please help me with the query for search ?
eg.
A1
A11
A12
A123
A13
B1
B123
C1
C11
C12
If I search for %123% the result -
A1
A12
A123
B1
B123

My idea initially was, yeah possible BUT HOW. I was sure it needed two result sets and joining to form a query to get the output. But wait, what is internet saying about it. 

I stumbled upon this brilliant article in orafaq and I was sure my initial thought was wrong. I was in a different mindset. Thanks to the article and Oracle SQL the query was much simpler. But with a catch, the hierarchy was in reverse.

SELECT lpad(' ', LEVEL, '-') || h.title descr
FROM   test_hier h
CONNECT BY id = PRIOR parent_id
START  WITH parent_id IS NOT NULL
     AND    h.title LIKE '%123%'
And the output was:

DESCR
 A123
- A12
-- A1
 B123
- B1


No comments :

Post a Comment