recursion - Oracle recursive hierarchical query with condition on the leaf level -

i want hierarchical query condition on leaf level.

i query filter father - child relationships leaf level statisfies condition : id '3%'

the table t :

parentid,id insert t values ('vte', 'vtp'); insert t values ('vtp', '202'); insert t values ('ser', '606'); insert t values ('ser', '609'); insert t values ('gif', '301'); insert t values ('ech', '302'); insert t values ('pub', 'mer'); insert t values ('mer', '312'); insert t values ('mer', '313'); insert t values ('mer', '314'); insert t values ('mes', '318'); insert t values ('pub', 'pre'); insert t values ('pub', 'pap'); insert t values ('sta', '317'); insert t values ('niv', 'ans'); insert t values ('znm', '497'); insert t values ('znu', '496'); insert t values ('ans', 'znc'); insert t values ('znc', '491'); insert t values ('nul', 'niv'); insert t values ('niv', 'vte'); insert t values ('vte', 'vtc'); insert t values ('vtc', '100'); insert t values ('vtp', '204'); insert t values ('vtp', '205'); insert t values ('vta', '500'); insert t values ('ser', '600'); insert t values ('niv', 'pub'); insert t values ('ech', '303'); insert t values ('mer', '305'); insert t values ('mer', '306'); insert t values ('mer', '309'); insert t values ('pap', '605'); insert t values ('sep', 'pbc'); insert t values ('pbc', '601'); insert t values ('sep', 'sta'); insert t values ('niv', 'tra'); insert t values ('znp', '498'); insert t values ('ans', 'znm'); insert t values ('ans', 'zne'); insert t values ('ans', 'znr'); insert t values ('znr', '493'); insert t values ('znf', '492'); insert t values ('vtc', '101'); insert t values ('vtc', '102'); insert t values ('vte', 'vta'); insert t values ('vte', 'ser'); insert t values ('aut', '900'); insert t values ('pub', 'cpr'); insert t values ('mer', '310'); insert t values ('mer', '311'); insert t values ('mer', '604'); insert t values ('pub', 'mes'); insert t values ('mes', '316'); insert t values ('sep', 'rsf'); insert t values ('rsf', '608'); insert t values ('tra', 'trp'); insert t values ('trp', '603'); insert t values ('ans', 'znp'); insert t values ('ans', 'znu'); insert t values ('ans', 'zng'); insert t values ('ans', 'znf'); insert t values ('vtc', '104'); insert t values ('vtc', '105'); insert t values ('vtp', '200'); insert t values ('vtp', '201'); insert t values ('vtp', '203'); insert t values ('vta', '400'); insert t values ('vte', 'aut'); insert t values ('cpr', '602'); insert t values ('pub', 'gif'); insert t values ('pub', 'ech'); insert t values ('mer', '307'); insert t values ('mer', '308'); insert t values ('pre', '304'); insert t values ('pre', '315'); insert t values ('niv', 'sep'); insert t values ('trp', '607'); insert t values ('ans', 'zna'); insert t values ('zna', '499'); insert t values ('zng', '495'); insert t values ('zne', '494'); commit; 

i initialy thought use :

select parentid, id t  id '3%'  start parentid ='niv' connect prior id = parentid 

for example, 302's father ech ech's father pub pub's father niv

but query displays level 0 relationships : 302's father ech

it discards higher levels root : ech's father pub pub's father niv

i came solution below.

but, child-parents loop hard coded, defeats purpose of hierarchical query number of child - parent relationships automatically identified.

select parentid, id t id in    (select parentid    t        id in       (select parentid       t       id '3%'       start parentid='niv'        connect prior id = parentid)       start parentid ='niv'        connect prior id = parentid) or  ccomuse in    (select parentid     t    id '3%'    start parentid ='niv'     connect prior id = parentid ) or  id '3%'  start parentid ='niv'  connect prior id = parentid  

is there way extract child-parent relationships without hard coding 2 inside loops, ie recursive method automatically reach root ?

have looked @ sys_connect_by_path() function itemise elemnts in hierarchy, , filter on right-most?

http://docs.oracle.com/cd/b28359_01/server.111/b28286/functions171.htm#i1038266