Link to home
Start Free TrialLog in
Avatar of Reeds
Reeds

asked on

Group function nested too deeply using XMLagg

I have a query which returns XML, when I run the below I get "group function nested too deeply" because of XMLAgg being used 3 times. But I need it since I want to aggregate the results. Please help


select xmlelement("units",
       xmlagg(
            xmlelement("unit",
                xmlattributes(
                    u.unitid as "unitid",
                    u.code as "unitcode",
                    u.name as "unitname"
                ),
              xmlagg(
               xmlelement("datasets",
                     xmlelement("dataset",
                        xmlattributes(
                           d.datasetid as "id",
                           d.name as "name",
                           u.unitid || d.datasetid as "unitdatasetid"
                       ),
                      xmlagg(
                        xmlelement("journal",
                            xmlattributes(
                                 u.code as "unitcode",
                                 j.id as "journalid",
                                 j.je_code as "journalcode",
                                 d.datasetid as "datasetid",
                                 d.name as "datasetname",
                                 j.id ||  u.unitid || d.datasetid as "jrlunitdatasetid"
                              )
                            )
                        )
                    )
                )
            )
        )
    )
    
  
  )
    --into v_units
    from unitdatasets uds
    inner join units u on u.unitid = uds.unitid
    inner join unitsubcategories us on us.unitid = u.unitid
    inner join reportingdatasetmembers rdsm on rdsm.datasetid = uds.datasetid
    inner join datasets d on d.datasetid = rdsm.datasetid
    inner join journals j on j.unitid = us.unitid and j.datasetid = uds.datasetid
    where       and us.subcategoryid = 1057
        and rdsm.reportingdatasetid = 1608438
    group by  d.datasetid, d.name,u.unitid, u.code, u.name
    order by u.code;

Open in new window

Avatar of schwertner
schwertner
Flag of Antarctica image

Two remarks:
1. I do not see the group function

2. You use SQL-99 syntax. What if you ttry legacy Oracle join syntax:


  units.unitid = uds.unitid

instead

inner join units u on u.unitid = uds.unitid

Avatar of Reeds
Reeds

ASKER

I get the error on XMLAgg on using it more than twice. It works if I remove one XMLAgg.
Avatar of Reeds

ASKER

 units.unitid = uds.unitid gives the same result. Group by is at line number 46
 
can you provide ddl for your tables so I can try to replicate the problem?

also, it appears your where clause isn't correct
some sample data would be helpful too
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Reeds

ASKER

Thanks. I did this which seems to work:
select xmlelement("units",
       xmlagg(
            xmlelement("unit",
                xmlattributes(
                    result.unitid as "unitid",
                    result.code as "unitcode",
                    result.unitname as "unitname"
                ),
              xmlagg(
                   xmlelement("dataset",
                        xmlattributes(
                          result.datasetid as "id",
                          result.name as "name"--,
                          --result.unitid || result.datasetid as "unitdatasetid"
                       ),
                       xmlelement("postid",
                     result.jdata
                    )
                )
            )
        )
   )
   --into v_units
   from
   (
      select u.unitid, u.code, u.name unitname,d.datasetid , d.name,
              xmlagg(
            xmlelement("journal",
                xmlattributes(
                     u.code as "unitcode",
                     j.id as "journalid",
                     j.je_code as "journalcode",
                     j.postid as "postid",
                     d.datasetid as "datasetid",
                     d.name as "datasetname"--,
                     --j.id ||  u.unitid || d.datasetid as "jrlunitdatasetid"
                  )
                )
            ) as jdata
    from
    unitdatasets uds
    inner join units u on u.unitid = uds.unitid
    inner join unitsubcategories us on us.unitid = u.unitid
    inner join reportingdatasetmembers rdsm on rdsm.datasetid = uds.datasetid
    inner join datasets d on d.datasetid = rdsm.datasetid
    inner join journals j on j.unitid = us.unitid and j.datasetid = uds.datasetid
    where 1 = 1
        and us.subcategoryid = 1032
        and rdsm.reportingdatasetid = 1608436
    group by  u.unitid, u.code,u.name,  d.datasetid, d.name--, j.postid
    order by u.code) result
    group by result.unitid, result.code, result.unitname;
Avatar of Reeds

ASKER

I have a follow up question, now I want to group the journal aggregation by postid. If I change the query to:
select xmlelement("units",
       xmlagg(
            xmlelement("unit",
                xmlattributes(
                    result.unitid as "unitid",
                    result.code as "unitcode",
                    result.unitname as "unitname"
                ),
              xmlagg(
                   xmlelement("dataset",
                        xmlattributes(
                          result.datasetid as "id",
                          result.name as "name"--,
                          --result.unitid || result.datasetid as "unitdatasetid"
                       ),
                     
                     result.jdata
                    )
                )
            )
        )
   )
   --into v_units
   from
   (
      select u.unitid, u.code, u.name unitname,d.datasetid , d.name,j.postid,
      xmlelement("postid",
          xmlagg(
            xmlelement("journal",
                xmlattributes(
                     u.code as "unitcode",
                     j.id as "journalid",
                     j.je_code as "journalcode",
                     j.postid as "postid",
                     d.datasetid as "datasetid",
                     d.name as "datasetname"--,
                     --j.id ||  u.unitid || d.datasetid as "jrlunitdatasetid"
                  )
                )
            )
    ) as jdata
    from
    unitdatasets uds
    inner join units u on u.unitid = uds.unitid
    inner join unitsubcategories us on us.unitid = u.unitid
    inner join reportingdatasetmembers rdsm on rdsm.datasetid = uds.datasetid
    inner join datasets d on d.datasetid = rdsm.datasetid
    inner join journals j on j.unitid = us.unitid and j.datasetid = uds.datasetid
    where 1 = 1
        and us.subcategoryid = 1032
        and rdsm.reportingdatasetid = 1608436
    group by  u.unitid, u.code,u.name,  d.datasetid, d.name, j.postid
    order by u.code) result
    group by result.unitid, result.code, result.unitname;

I get the following output:


<units>
    <unit unitid="1031" unitcode="000131" unitname="Bristol-Myers Squibb Business Services Limited">
        <dataset id="1608435" name="2008 December">
            <postid>
                <journal unitcode="000131" journalid="51" journalcode="JOURNAL21" postid="1" datasetid="1608435" datasetname="2008 December"></journal>
                <journal unitcode="000131" journalid="50" journalcode="JOURNAL11" postid="1" datasetid="1608435" datasetname="2008 December"></journal>
            </postid>
        </dataset>
        <dataset id="1608435" name="2008 December">
            <postid>
                <journal unitcode="000131" journalid="53" journalcode="JOURNAL22" postid="2" datasetid="1608435" datasetname="2008 December"></journal>
                <journal unitcode="000131" journalid="52" journalcode="JOURNAL12" postid="2" datasetid="1608435" datasetname="2008 December"></journal>
            </postid>
        </dataset>
        <dataset id="1608435" name="2008 December">
            <postid>
                <journal unitcode="000131" journalid="55" journalcode="JOURNAL23" postid="3" datasetid="1608435" datasetname="2008 December"></journal>
                <journal unitcode="000131" journalid="54" journalcode="JOURNAL13" postid="3" datasetid="1608435" datasetname="2008 December"></journal>
            </postid>
        </dataset>
    </unit>
</units>

But the output I want is:


<units>
    <unit unitid="1031" unitcode="000131" unitname="Bristol-Myers Squibb Business Services Limited">
        <dataset id="1608435" name="2008 December">
            <postid>
                <journal unitcode="000131" journalid="51" journalcode="JOURNAL21" postid="1" datasetid="1608435" datasetname="2008 December"></journal>
                <journal unitcode="000131" journalid="50" journalcode="JOURNAL11" postid="1" datasetid="1608435" datasetname="2008 December"></journal>
            </postid>
            <postid>
                <journal unitcode="000131" journalid="53" journalcode="JOURNAL22" postid="2" datasetid="1608435" datasetname="2008 December"></journal>
                <journal unitcode="000131" journalid="52" journalcode="JOURNAL12" postid="2" datasetid="1608435" datasetname="2008 December"></journal>
            </postid>
             <postid>
                <journal unitcode="000131" journalid="55" journalcode="JOURNAL23" postid="3" datasetid="1608435" datasetname="2008 December"></journal>
                <journal unitcode="000131" journalid="54" journalcode="JOURNAL13" postid="3" datasetid="1608435" datasetname="2008 December"></journal>
            </postid>
        </dataset>
    </unit>
</units>

I have been trying to group it by postid but it doesn't seem to work. Help...
since this question has been answered,

please open a new question for your followup
in it please post the results of the inner query as well as the entire query so we can see the inner aggregations at work.
and of course, the base data for these.


or, at bare minimum  provide the data for the result of


select <all relevant columns>
 from unitdatasets uds
    inner join units u on u.unitid = uds.unitid
    inner join unitsubcategories us on us.unitid = u.unitid
    inner join reportingdatasetmembers rdsm on rdsm.datasetid = uds.datasetid
    inner join datasets d on d.datasetid = rdsm.datasetid
    inner join journals j on j.unitid = us.unitid and j.datasetid = uds.datasetid
    where 1 = 1
        and us.subcategoryid = 1032
        and rdsm.reportingdatasetid = 1608436