xmloracle-xml-db

ORA-00937: not a single group function (XMLAGG)


I used the XMLAGG aggregate function in a single row and I keep getting the error code ORA-00937. Why?

The structure for the report is:

<Forms>
    <Year>2015</Year>
    <Month>September</Month>
    <Date>2015-09-22</Date>
    <Form>A1</Form>
    <Form>B4</Form>
    …
</Forms>

My select statement is:

SELECT XMLSERIALIZE(
  DOCUMENT XMLROOT(
     XMLELEMENT("Forms",XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-    instance' AS "xmlns:xsi"),
         XMLELEMENT("Year", b.Year),
         XMLELEMENT("Month", b.Month),
         XMLELEMENT("Date", b.r_modify_date),
            XMLAGG(XMLELEMENT"Form",b.docnum))               
       ), 
      )  
    ) 
 FROM xml_bill  b
 where trunc(b.R_MODIFY_DATE) =trunc(sysdate);

Solution

  • So I added GROUP BY in the WHERE clause and modified XMLELEMENT(“Date”, trunc(b.r_modify_date) in the SELECT statement

    Here is the new query:

    SELECT XMLSERIALIZE(
            DOCUMENT XMLROOT(
                XMLELEMENT("Forms",XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"),
                    XMLELEMENT("Year", b.Year),
                    XMLELEMENT("Month", b.Month),
                    XMLELEMENT("Date", trunc(b.R_MODIFY_DATE)),
                        XMLAGG(
                                XMLELEMENT("Form", b.DOCNUM)
                               )
                    ),
                )  
        )
     FROM xml_bill  b
    where
    trunc(b.R_MODIFY_DATE) =trunc(sysdate)
    GROUP BY b.Year, b.Month,trunc(b.R_MODIFY_DATE);