CODEWITHSUNDEEP
xmloracle-xml-db
Caramel2017
Caramel2017

Reputation: 23

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);

Upvotes: 2

Views: 2573

Answers (1)

David Neiss
David Neiss

Reputation: 8237

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);

Upvotes: 1

Related Questions