hi all, has anybody managed to get a either SYS_CONNECT_BY_PATH or CONNECT_BY_ROOT working on Hierarchical Queries? This is my query

WITH DATA AS (
                                          SELECT parentitm.itemid parentname, itemmember.parentitem parent, itemmember.item itemid,
                                                 item.itemtype itemtype, itemmember.itemsource itemsource,
                                                 item.itemid componentname,
                                                 itemmember.quantity quantity,
                                                 itemmember.memberunit memberunit,
                                                 itemmember.linenumber linenumber,
                                                 aggregateItemLocation.location location,
                                                 BUILTIN.DF(aggregateItemLocation.location) locationname,
                                                 aggregateItemLocation.quantityavailable qtyonhand
                                          FROM itemmember
                                                   LEFT JOIN item ON itemmember.item = item.id
                                                   LEFT JOIN item parentitm ON itemmember.parentitem = parentitm.id
                                                   LEFT JOIN aggregateItemLocation ON aggregateItemLocation.item = itemmember.item
                                          WHERE parentitm.isinactive = 'F'  AND aggregateItemLocation.location = '131'
                                      )
                                      SELECT parent, parentname, itemid, componentname, itemtype, itemsource, quantity,
                                             memberunit, location, locationname, qtyonhand, linenumber, LEVEL componentlevel,
                                             LEVEL||'-'||linenumber componentlevelwithline
-- , SYS_CONNECT_BY_PATH(parentname, '-')
                                      FROM DATA
                                               START WITH parent IN('207264', '207265')
                                      CONNECT BY PRIOR  itemid = parent
				      ORDER SIBLINGS BY itemid

if you uncomment

-- , SYS_CONNECT_BY_PATH(parentname, '-')

this query will fail, with it commented it will work but I can not tell which components are for item 207264 or 207265 any ideas really appreciate.

I've tried in the past and got to the same point as you. There is also a

BUILTIN.HEIRARCHY

which I've never managed to get working either

@michoel thanks for the response. yeah the bultin.heirarchy I think is for things like the locations table..... i couldn't make head nor tales of of using that builtin for anything locations and sub.

sorry this isn't an answer, but thanks for your question cause I never knew about heirarchical queries and just read a bit about them.