BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
I'm having trouble combining two pieces of sql using xml. I'm building an array of "items" using a select statement that works just fine. (contained_by is the parent of all the children in the array with the same contained by value)
SELECT XMLELEMENT("itemArray", XMLAGG(extract(xml_data,'/item')))
FROM description_test
WHERE contained_by = 181315;
<itemArray>
<item>...</item>
<item>...</item>
<item>...</item>
</itemArray>
Now, when I try to "attach" the parent, I can't seem to get it to work.
SELECT p.naId, XMLCONCAT(extract(p.xml_data,'/fileUnit'),
XMLAGG(XMLELEMENT("itemArray", extract(c.xml_data,'/item'))))
FROM description_test p --parent,
description_test c --child
WHERE c.contained_by = p.naid;
ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"
SELECT p.naId, XMLCONCAT(extract(p.xml_data,'/fileUnit'),
XMLAGG(XMLELEMENT("itemArray", extract(c.xml_data,'/item'))))
FROM description_test p, --parent,
description_test c --child
WHERE p.desc_type = 'FU'
AND c.contained_by = p.naid
and rownum < 5
group by p.naid;
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
what I'm expecting is the following to come back :
<fileUnit>
<some_data/>
<more_data/>
<itemArray>
<item>...</item>
<item>...</item>
<item>...</item>
</itemArray>
</fileUnit>
Not sure why this is not working. If I do the same thing without trying to build the xml array I get the following:
SELECT p.naId, count(c.naid) AS CHILD_COUNT
FROM description_test p,
description_test c
WHERE c.contained_by=p.naid
group by p.naid;
NAID CHILD_COUNT
135801 25
174634 1
137211 15
136529 30
143455 36
145696 42
Help is greatly appreciated. Thanks!!!