Solved

Oracle XML Group by issue.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

729 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