create a view with listagg of connect by hierarchy

[jeff kish]

Hi.

I have a table that includes these columns:
assetnum,description,parent.

Given any assetnum I can find its parent using the parent field. I can build a string showing the hierarchy for any given asset, say '268180' using this query:

select LISTAGG('(' || assetnum || ') ' || description, ' / ') WITHIN GROUP (order by level desc) "assetlist" from asset start with assetnum = '268180' connect by prior parent = assetnum;

I'd like much to create a view which has this information for any asset, but I can't get my head around how to get it working.

I've tried various things such as
create view TEST as
select LISTAGG('(' || assetnum || ') ' || description, ' / ') WITHIN GROUP (order by level desc) "assetlist" from asset start with assetnum = assetnum connect by prior parent = assetnum;

and

create view TEST as
select LISTAGG('(' || assetnum || ') ' || description, ' / ') WITHIN GROUP (order by level desc) "assetlist" from asset start with parent = null connect by prior parent = assetnum;


but nothing is working. I'm having a conceptual problem here. Can someone help me out figuring the sql to create the view in oracle 11? Any explanation along the way is gratefully appreciated.

Jeff

[jeff kish]

I found this works fyi
Jeff

select a.assetnum, a.siteid,
(select LISTAGG('(' || b.assetnum || ') ' || b.description, ' / ') WITHIN GROUP (order by level desc) "ASSETPARENTHIERARCHY"
from asset b start with assetnum = a.assetnum and siteid = '001' connect by prior parent = assetnum) ASSETPARENTHIERARCHY
from asset a