Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle XML Group by issue.

Posted on 2013-11-26
5
Medium Priority
?
862 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 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

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 …
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

927 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