Solved

Oracle XML Group by issue.

Posted on 2013-11-26
5
748 Views
Last Modified: 2013-12-07
Hi,

This is in continuation to my earlier question for Oracle XML Hierarchical output.

I am using the below query to spool an XML file. When I am running it, I am getting ORA-00937: not a single-group group function near mycount as "OrdersCount".

Could you please look into it and let me know?

Thanks,
(Raj) Sudhakar.


QUERY:
with mydata as (select hospital_code,medical_code,medical_desc,amount,stdt,enddt,details,count(*) over() mycount
from  meddetails m)

SELECT
xmlserialize(document
XMLELEMENT("MedDetails",
           XMLELEMENT(
               "HeaderInformation",
               XMLFOREST(
                   'EMPR-Data' AS "DataSource",
                   TO_CHAR(SYSDATE, 'mm/dd/yyyy hh:mi:ss AM') AS "DateCreated",
                   mycOUNT AS "EMPCount"
               )
           ),
           XMLAGG(
            XMLELEMENT(
                     "MEDINFO",
                     XMLELEMENT("HCD",
                     XMLAGG(
                         XMLELEMENT("M",
                             xmlattributes(medical_code AS "MEDICAL_CODE"),
                             XMLELEMENT("E",
                                 XMLFOREST(
                                     amount AS "Amount",
                                     stdt AS "StDt",
                                     enddt AS "EnDt",
                                     details AS "Details"
                                 )
                             )
                         )
                     )
                 )
            )
      )
)
version '1.0' indent)
            FROM mydata
        GROUP BY mycount,hospital_code
/
0
Comment
Question by:nssudha
  • 4
5 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39678086
please post structure, sample data and expected output
0
 

Author Comment

by:nssudha
ID: 39678118
If I comment out - mycOUNT AS "EMPCount", I get the desired output.

I am posting sample data and the desired output.

Thanks,
(Raj) Sudhakar.
MyData.xml
MedicalData.xls
0
 

Author Comment

by:nssudha
ID: 39678473
I narrowed down the issue to XMLAGG at the below step. If I comment XMLAGG here, I am able to execute the count. But I will get multiple clobs for the result. Whereas I need one CLOB / XML document in the result (including the count for the number of records in the output).

Thanks,
(Raj) Sudhakar.

XMLAGG(
            XMLELEMENT(
                     "MEDINFO",
0
 

Accepted Solution

by:
nssudha earned 0 total points
ID: 39690225
I found my own solution. It is a bit of workaround with three sql scripts. I got it worked through a block and spooled the output through a temporary table to xml.

Thanks,
(Raj) Sudhakar.
0
 

Author Closing Comment

by:nssudha
ID: 39702872
Since no expert got a direct solution. I got it worked through my own solution.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question