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
