使用 xmllagg 的子查询错误和太多值 [英] subquery eror and too many values using xmllagg

问题描述

我在连接许多大表中的所有数据时遇到问题.我昨天就此提出了问题，但不幸的是，listagg 似乎不是一个好的选择.链接

我确实得到了一小部分测试结果，但是当我尝试使用完整代码时，只需查看此代码，它就不起作用，请查看错误下方的代码和错误:子查询返回多于一行

 选择NVL(TO_CHAR(D_TRANS.TRANS), 'NULL') AS ID,'标题', (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(),',')来自 L_CASE_CATEGORY在 D_SYNERGI_CATEGORY.CASE_CATEGORY = L_CASE_CATEGORY.CASE_CATEGORY 上左加入 D_SYNERGI_CATEGORYGROUP BY D_SYNERGI_CATEGORY.TRANS))发件人 D_TRANS全外连接 D_SPILLON D_TRANS.TRANS=D_SPILL.TRANSORDER BY D_TRANS.TRANS DESC;

当我测试下面的这段代码时，它正在工作.

 (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,',').提取('//文本()')).GetClobVal(),',')来自 L_CASE_CATEGORY在 D_SYNERGI_CATEGORY.CASE_CATEGORY = L_CASE_CATEGORY.CASE_CATEGORY 上左加入 D_SYNERGI_CATEGORYGROUP BY D_SYNERGI_CATEGORY.TRANS)

我不是 Oracle 专家.有什么建议吗?

解决方案

xmlagg和xQuery的结合，不简单.

select xmlquery('distinct-values(//text())'传递 x 返回内容).getclobVal(),data_type from (选择 xmlelement(root, xmlagg(XMLELEMENT(e,table_name,','))) x ,data_type来自 user_tab_cols where data_type in ('VARCHAR2','NUMBER')按数据类型分组)

为了你的目的，它应该是这样的

选择应用程序，key_event，xmlquery('distinct-values(//text())' 传递 xmldoc 返回内容).getclobVal()从(选择t2.app，合并(最大(语言 = 2 时的情况，然后描述结束)，最大(语言 = 12 时的情况，然后描述结束)，最大(语言 = 27 时的情况，然后描述结束)，'NULL') key_event,XMLELEMENT(root, xmlagg(XMLELEMENT(e, description, ','))) 文档从表2 t2左连接t1.app = t2.app 上的 table1 t1通过...分组反式，t2.app按反式订购)；

如果你分解这个查询，你会看到它是如何工作的.

xquery 不同值语法

xmlquery 语法

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 ?

解决方案

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

这篇关于使用 xmllagg 的子查询错误和太多值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！