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...