Re: create a view with listagg of connect by hierarchy

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

On Tuesday, October 30, 2012 1:07:59 PM UTC-4, jeff kish wrote:
> 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
Received on Wed Oct 31 2012 - 01:02:01 CET