1

Topic: Question on demand

Good afternoon. There is a following structure of basis (it is specified in nesting).

The job:

For each separation (separation is the subdividing which title begins with a word "Squad")
To enumerate through a comma in one line all staff, arranging military men alphabetically.
To consider only the military men attributed immediately to separation.
Output: the first column - ID the subdividings, the second - a name list of military men (name) through a comma (without gaps).
I write the following request to such basis:

SELECT unit_id, (
SELECT regexp_replace (sys_connect_by_path (name, ', '), ' ^, ', ")
FROM staff
WHERE connect_by_isleaf = 1 and rownum <2
start with unit_id = mu.unit_id
connect by NOCYCLE unit_id = mu.unit_id and (chief = prior person_id or chief = prior chief)
ORDER SIBLINGS BY name
)
from military_units mu
where name like ' Squad %'

But for some reason produces an error:
ORA-00907: missing right parenthesis
On visible, I anywhere did not pass brackets. Noted that when I remove a line:

ORDER SIBLINGS BY name

That the error disappears. Prompt please, in what business.

2

Re: Question on demand

I send structure :
[img=http://sb.edu-netcracker.com/resources/images/schemas/mil_staff.PNG]

3

Re: Question on demand

Pavel97;
Through LISTAGG it was necessary to do

4

Re: Question on demand

Pavel97;
order by in subqueries at this level does not work

5

Re: Question on demand

env;
And how, in that case, to filter a line so that surnames went in alphabetic order?

6

Re: Question on demand

Pavel97;

hck1 wrote:

Pavel97;
Through LISTAGG it was necessary to do

All the same I advise to esteem about LISTAGG