Solved

Oracle XML Group by issue.

Posted on 2013-11-26
5
761 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 68
Email query results in HTML 6 38
Fill Null values 5 36
Oracle Query to Find number of process consumed by a session 15 54
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

733 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