create a view with listagg of connect by hierarchy

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 Received on Tue Oct 30 2012 - 18:07:59 CET