Home » Developer & Programmer » JDeveloper, Java & XML » XML -- NOT A SINGLE GROUP GROUP FUNCTION (Oracle 11g, XP)
XML -- NOT A SINGLE GROUP GROUP FUNCTION [message #597234] Tue, 01 October 2013 18:39 Go to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Hi,

Firstly table structure :

  Create table patient (pat_mrn varchar2(100)) ;
  Create table encount (pat_mrn varchar2(100), encounter_id varchar2(1000));
  
 Insert into patient values ('63280');
 Insert into encount values ('63280', '42');

create table encounter_dx (CSN_ID VARCHAR2(100), encounter_id varchar2(100), dx_id varchar2(100));
Insert into encounter_dx values (300, 42, 106);
Insert into encounter_dx values (300, 42, 107);


Create table dx (dx_id varchar2(100),ic varchar2(100));
Insert into dx values (106, 410);
Insert into dx values (106, 420);




O/p :

<EncounterDiagnosis>
<CSN>300</CSN>
<Diagnosis>
<ID>106</ID>
<DiagnosisCodes>
<Code>410</Code>
<Code>420</Code>
</DiagnosisCodes>
</Diagnosis>
</EncounterDiagnosis>


Code I wrote :

SELECT
(
SELECT 
 XMLELEMENT("EncounterDiagnosis",
      XMLELEMENT("PrimaryDiagnosis",CE.CSN_ID), -- Once I include this I get an error saying not a group group function
                    XMLAGG(XMLELEMENT("Diagnosis",
                                            XMLELEMENT("ID", CE.DX_ID),
                                            XMLELEMENT("DiagnosisCodes",
                                                       XMLAGG(XMLELEMENT("Code",
                                                                         DX.IC))))))
        
          FROM encounter_dx CE, DX 
         WHERE CE.ENCOUNTER_ID = E.encounter_id
          AND  CE.DX_id = DX.DX_ID
         GROUP BY CE.DX_ID) 
         AS Orderxml

  FROM PATIENT P,ENCOUNT E
 WHERE P.Pat_Mrn = E.Pat_Mrn(+);



Thanks.
Re: XML -- NOT A SINGLE GROUP GROUP FUNCTION [message #597271 is a reply to message #597234] Wed, 02 October 2013 08:07 Go to previous message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
I had this which achieves the above. Any better approach than this :

SELECT p.pat_mrn,
       e.encounter_id,
       (SELECT XMLElement("EncounterDiagnosis",
                          XMLElement("CSN", ce.csn_id),
                          XMLAgg((SELECT XMLAgg(XMLElement("Diagnosis",
                                                          XMLElement("ID",
                                                                     dx.dx_id),
                                                          XMLElement("DiagnosisCodes",
                                                                     XMLAgg(XMLElement("Code",
                                                                                       dx.ic)))))
                                   FROM dx
                                  WHERE dx.dx_id = ce.dx_id
                                  GROUP BY dx.dx_id)))
          FROM encounter_dx ce
         WHERE ce.encounter_id = e.encounter_id
         GROUP BY ce.csn_id) AS Orderxml
  FROM patient p
  LEFT OUTER JOIN encount e
    ON p.pat_mrn = e.pat_mrn


Previous Topic: XMLAGG for clob fields
Next Topic: Runtime error ORA-29515
Goto Forum:
  


Current Time: Wed Oct 01 03:40:08 CDT 2014

Total time taken to generate the page: 0.32906 seconds