> 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