查询中的Sum Listagg

Question

我正在尝试将我创建的listagg查询中的值进行求和。

预期的结果应该是这样

SELECT LEGAL_ENTITY_ID, SUM(0 + 0 + 0 + 1) as Grandtotals
FROM V_VBA_DDCR_MAIN WHERE LEGAL_ENTITY_ID=6012346 AND ROWNUM=1
GROUP BY LEGAL_ENTITY_ID

 |      LEGAL_ENTITY_ID | GRANDTOTALS 
1|    6012346           | 1

我的名单是这样的

SELECT LISTAGG(NVL2(FLDNM,0,1) , '+ ') WITHIN GROUP (ORDER BY FLDNM) FROM LE_MERGE_DDC_MAPPING

此查询的目的是计数一行中的空字段数。我创建了一个包含需要检查空值的字段列表的表。

结果：

    fld1 + fld2 + fld3 + fld4
=
        0 + 0 + 0 + 1

所以我写了这样的查询：

SELECT LEGAL_ENTITY_ID, SUM(SELECT LISTAGG(NVL2(FLDNM,0,1) , '+ ') WITHIN GROUP 
(ORDER BY FLDNM) FROM LE_MERGE_DDC_MAPPING) as Grandtotals
FROM V_VBA_DDCR_MAIN WHERE LEGAL_ENTITY_ID=6000132
GROUP BY LEGAL_ENTITY_ID

但是，我得到了一个错误ORA-00936:缺失表达式。我不确定Oracle是否允许您完成listagg函数的和。任何帮助都是非常感谢的。

Answer 1

SELECT LISTAGG(NVL2(FLDNM,0,1) , '+ ')...查询将始终得到字符串结果'0+ 0+ 0+ ...'，除非LE_MERGE_DDC_MAPPING中有为null的行。该值与V_VBA_DDCR_MAIN表无关。您可以生成包含以下表达式的字符串：

SELECT LISTAGG('NVL2(' || FLDNM || ' ,0,1)' , '+ ') ...

但这只会给您留下一个包含'NVL2(fld1 ,0,1)+ NVL2(fld2 ,0,1)+ ...'的字符串。您不能对字符串进行求和，sum()也不能将字符串计算为表达式。您需要基于该字符串生成一个动态SQL语句。

有一种使用XML的方法，这并不完全是直观的。您可以使用dbms_xmlgen包创建一个XML (作为CLOB)，该文档包含一个具有来自实际目标表的NVL2结果的节点：

select dbms_xmlgen.getxml('select nvl2(' || fldnm || ',0,1) from v_vba_ddcr_main where legal_entity_id=6012346')
from le_merge_ddc_mapping map;

我不会在这里显示生成的XML。然后，您可以查询该值以获取单个值：

select xmlquery('/ROWSET/ROW/*/text()'
  passing xmltype(
    dbms_xmlgen.getxml('select nvl2(' || fldnm || ',0,1) from v_vba_ddcr_main where Legal_Entity_Id=6012346')
  ) 
  returning content)
from le_merge_ddc_mapping;

你可以把它们加起来：

select sum(to_number(xmlquery('/ROWSET/ROW/*/text()'
  passing xmltype(
    dbms_xmlgen.getxml('select nvl2(' || fldnm || ',0,1) from v_vba_ddcr_main where legal_entity_id=6012346')
  )
  returning content))) as grandtotals
from le_merge_ddc_mapping;

您已经建议在表之间有一个链接，以确定包含了哪些字段，因此您需要添加这些字段。您已经说过有重复的，您需要处理-最好通过从您的视图中删除它们，但如果必要的话使用子查询。这可以成为你发展的起点。

Answer 2

为什么要使用LISTAGG呢？

SELECT LEGAL_ENTITY_ID,
       (
         SELECT COUNT(1)
         FROM   LE_MERGE_DDC_MAPPING
         WHERE  FLDNM IS NULL
       ) as Grandtotals
FROM   V_VBA_DDCR_MAIN
WHERE  LEGAL_ENTITY_ID=6000132;

(由于您正在对单个LEGAL_ENTITY_ID进行过滤，所以不需要最终的GROUP BY__)

您确定这两个表之间不应该有某种连接条件吗？