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

Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
schwertner

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

ASKER
Reeds

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

 units.unitid = uds.unitid gives the same result. Group by is at line number 46
 
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sean Stuber

can you provide ddl for your tables so I can try to replicate the problem?

also, it appears your where clause isn't correct
Sean Stuber

some sample data would be helpful too
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Reeds

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;
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Reeds

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...
Sean Stuber

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