subquery eror and too many values using xmllagg

Question

I have issue concatenating all of my data from many large tables. I asked question yesterday regarding this but unfortunately seems listagg is not good option. the link subquery return more than one row

I tried to use the xmllagg after listagg using to truncate is not possible with my version of oracle 12.0.1 , the first code as seen below is given subquery is returning more than one row...

SELECT rtrim(xmlagg(XMLELEMENT(e,table1.DESCRIPTION,',').EXTRACT  ('//text()')
              ).GetClobVal(),',') 
    FROM table1

 left  join table2 on  table1.app = table2.app
 AND LANGUAGE = 2
   GROUP BY table2.app

The second code one using another method is still saying too many values in first line

     SELECT nvl(max(case when language = 2 then description end), 'NULL')      key_event,     rtrim(xmlagg(XMLELEMENT              (e,table1.DESCRIPTION,',').EXTRACT  ('//text()')
              ).GetClobVal(),',') 
    FROM table1

 left  join table2 on  table1.app = table2.app
 AND LANGUAGE = 2
   GROUP BY table2.app

I have tested these 2 codes in this link dbfiddle and it was working. I want the result to be as this link

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=40852eaeaa8f334f77364eef77ffbe68

I did get result testing it as small bit but when i try to use the full code just look at this code, it is not working please see the code and error below error :subquery return more than one row

              SELECT  
        NVL(TO_CHAR(D_TRANS.TRANS), 'NULL') AS ID, 
        'HEADER'

  , (SELECT L_APPLICATION.APPLICATION FROM L_APPLICATION   L_APPLICATION WHERE LANGUAGE = 2 AND APPLICATION = D_TRANS.APPLICATION) AS   CASE_TYPE
  , NVL(TO_CHAR(D_TRANS.UNIT_IN_CHARGE), 'NULL') AS UNIT_IN_CHARGE
  , NVL(TO_CHAR(D_TRANS.PERSON_IN_CHARGE), 'NULL') AS PERSON_IN_CHARGE
  , NVL(TO_CHAR(D_TRANS.STATUS), 'NULL') AS CASE_STATUS
   , NVL(TO_CHAR(D_TRANS.DEADLINE), 'NULL') AS INTERNAL_DEADLINE





            (SELECT  D_SYNERGI_CATEGORY.TRANS, nvl(max(case when language =    2 then description end), 'NULL') AS ADE ,  rtrim(xmlagg(XMLELEMENT   (e,L_CASE_CATEGORY.DESCRIPTION,',').EXTRACT('//text()')
  ).GetClobVal(),',') 
             FROM L_CASE_CATEGORY

        left join   D_SYNERGI_CATEGORY  on         D_SYNERGI_CATEGORY.CASE_CATEGORY =  L_CASE_CATEGORY.CASE_CATEGORY   

         GROUP BY  D_SYNERGI_CATEGORY.TRANS

                            ) 


                 )


                 FROM D_TRANS
                        FULL OUTER  JOIN D_SPILL
                     ON D_TRANS.TRANS=D_SPILL.TRANS


                    ORDER BY D_TRANS.TRANS DESC;

When I tested this small bit of this code below it is working.

                         (SELECT  D_SYNERGI_CATEGORY.TRANS, nvl(max(case    when language = 2 then description end), 'NULL') AS ADE ,  rtrim(xmlagg(XMLELEMENT(e,L_CASE_CATEGORY.DESCRIPTION,',').EXTRACT('//text()')
  ).GetClobVal(),',') 
      FROM L_CASE_CATEGORY

       left join   D_SYNERGI_CATEGORY  on  D_SYNERGI_CATEGORY.CASE_CATEGORY     =  L_CASE_CATEGORY.CASE_CATEGORY   

        GROUP BY  D_SYNERGI_CATEGORY.TRANS

                        )

I am not expert in Oracle. Any suggestion ?

"but unfortunately seems listagg is not good option" Why is this? — MT0, Feb 13 '19 at 09:24
Please [edit your question](https://stackoverflow.com/posts/54666218/edit) to include a [MCVE] including some sample data (as DDL/DML statements) and what your expected output for that data is. — MT0, Feb 13 '19 at 09:25
"I have tested these 2 codes in this link dbfiddle and it was working." Great .... use the code in those fiddles and your problem is solved. What is your question if you have posted working code? — MT0, Feb 13 '19 at 09:41
it is not working on my database it is giving subquery could not return more than one row and the second code is giving too many values in the line one using the nvl(max(case....) — great77, Feb 13 '19 at 09:47
Then as per my previous comment ... [edit your question](https://stackoverflow.com/posts/54666218/edit) to include a [MCVE] that demonstrates the problem. Add some sample data (as DDL/DML statements) so that the first query fails and the second query has too many values and include the error messages and your expected output in your question; instead of making us try to guess what the problem is from some code that you say is working but does not give the expected output but you don't tell us what the expected output is or where the problem is. — MT0, Feb 13 '19 at 09:51
Thanks MT0, I have put the full sample code that gives the error. Can you see why it is saying subquery error. — great77, Feb 13 '19 at 10:46

score 0 · Answer 1 · answered Feb 13 '19 at 09:20

0

with REGEXP_REPLACE we can make deduplication. Check this:

select  t2.app,  
       coalesce(max(case when language =  2 then description end), 
                max(case when language = 12 then description end), 
                max(case when language = 27 then description end), 
               'NULL') key_event,
        REGEXP_REPLACE(listagg(description, ',') within group (order by t1.description, t1.language), '([^,]+)(,\1)+', '\1') list
  from table2 t2 
  left join table1 t1 on t1.app = t2.app 
  group by trans, t2.app
  order by trans, trans;

answered Feb 13 '19 at 09:20

Derviş Kayımbaşıoğlu

28,492
4
50
72

If the problem is that `LISTAGG` is returning more than 4000 bytes of characters then this will not solve the problem as `REGEXP_REPLACE` is applied to the output of `LISTAGG` and this will have already failed before the duplicates are removed. – MT0 Feb 13 '19 at 09:37
I agree with you. But AFAIK, there is no easy convention of doing this rather than SP – Derviş Kayımbaşıoğlu Feb 13 '19 at 10:01

score 0 · Accepted Answer · edited Mar 31 '19 at 18:48

Combination of xmlagg and xQuery, it's not simple.

select xmlquery('distinct-values(//text())' passing x returning content).getclobVal(),data_type from (
    select   xmlelement(root, xmlagg(XMLELEMENT(e,table_name,','))) x ,data_type
     from user_tab_cols where data_type in ('VARCHAR2','NUMBER')     
     group by data_type
     )

And for you purpose it should look like this

select 
    app, key_event,  
    xmlquery('distinct-values(//text())' passing xmldoc returning content).getclobVal()
from 
    (select  
         t2.app,  
         coalesce(max(case when language =  2 then description end), 
                  max(case when language = 12 then description end), 
                  max(case when language = 27 then description end), 
                  'NULL') key_event,
         XMLELEMENT(root, xmlagg(XMLELEMENT(e, description, ','))
    ) xmldoc
from 
    table2 t2 
left join 
    table1 t1 on t1.app = t2.app 
group by 
    trans, t2.app
order by trans);

if you will decompose this query you will see how its work.

xquery distinct-values syntax

xmlquery syntax

Thanks. I will try this also. But honestly why this subquery error in Oracle — great77, Feb 13 '19 at 10:53
I have used this code as well, It works separetly but when combine with other code it is saying subquery can return more than one — great77, Feb 13 '19 at 11:18
Thanks. I used this with my other code and it fit well. The xmllagg alone is creating duplicate. — great77, Feb 14 '19 at 22:57

subquery eror and too many values using xmllagg

2 Answers2

Linked