?
Solved

Oracle XML Group by issue.

Posted on 2013-11-26
5
Medium Priority
?
802 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 74

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

801 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