Hey Santana,
For a quick solution (and in case this is an exam question, I thought
I might go about it a different way. Not a big fan of doing people's
homework *GRIN*), I wouldn't use a recursive query, I'd simply use
string aggregation (9i). Assuming you have to have the ">", I added
the REPLACE as it seems to cause problems with the xmlagg.
select NVL(substr(numeration,1,instr(numeration,'.')-1),numeration)
grouping
,REPLACE(rtrim (xmlagg (xmlelement (t, title || '^')).extract
('//text()'), '^'),'^','>') titles
from book
group by NVL(substr(numeration,
1,instr(numeration,'.')-1),numeration)
order by to_number(grouping)
I think that should give you the output you are looking for. Though
there wasn't in my test, there may be an issue in the order in which
they aggregate. Perhaps creating a sorted inline view of "book" might
resolve it. Anyway, this should give you a good start I think.
Good luck!
-AT.
On Jul 15, 6:51 am, Santana <paulito.sant...@gmail.com> wrote:
> Hi all.
> Can you help me with this problem :
>
> I have the "Book" table with "title" and "Numeration" :
>
> Book Numeration
> "USA History" 4
> "USA Sport History" 4.1
> "USA swimming History" 4.1.1
> "NATO" 9
> "NATO History" 9.1
> "NATO History Chairman" 9.1.1
> "BBC" 10
>
> and i need get as output this string : "USA History>USA Sport
> History>"USA swimming
> History"
> "NATO">"NATO
> History">"NATO History Chairman"
> "BBC"
>
> I can do this with a simple PLSQL program, but the problem is that my
> customer requires a query to do this.
> Its possible do this with the recursive conecept ?
>
> Something like this(the following query dont work well) :
>
> select CONNECT_BY_ROOT Numeration
> from Book
> where 1=1
> START WITH (Numeration not like '%.%' )
> CONNECT BY PRIOR Numeration ||'.%' = Numeration
>
> Thanks,
> Paulito Santana
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
