为什么 wm_concat 在这里不起作用?

2021-12-30 00:00:00 sql oracle11g oracle string-aggregation wm-concat

我有这个查询:

(SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
    (SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',','))))

返回:

但是当我这样做时:

SELECT wm_concat(object_id) FROM
    (SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
        (SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',','))))

我得到一个空白结果......我做错了什么?

I get a blank result... what am I doing wrong?

推荐答案

你必须避免使用 wm_concat 函数，因为它没有被记录并且在 Oracle 8i 时代被发现作为解决方法.

You must avoid wm_concat function because it is undocumented and discovered as workaround at Oracle 8i times.

自从 Tom Kyte 发现具有自定义聚合函数的旧方法以来 此处 有一些新的解决方法，如下面的示例所示.

Since times of old method with custom aggregate function as discovered by Tom Kyte here there are some new workarounds, showed at examples below.

所有这些都在这个 SQL Fiddle 中复制.

All of them reproduced in this SQL Fiddle.

解决方法 1 - LISTAGG 函数，适用于 11g:

Workaround 1 - LISTAGG function, works in 11g:

select listagg(object_id,',') within group (order by rownum) id_string
from cr_object_group_entries_vw

解决方法 2 - SYS_CONNECT_BY_PATH，从 10g 开始工作:

Workaround 2 - SYS_CONNECT_BY_PATH, works since 10g: 

select id_string from (
  select rn, substr(sys_connect_by_path(object_id, ','),2) id_string
  from (select object_id, rownum rn from cr_object_group_entries_vw)
  start with rn = 1
  connect by prior rn + 1 = rn
  order by rn desc
)
where rownum = 1

解决方法 3 - XMLAGG，从 10g 开始工作:

Workaround 3 - XMLAGG, works since 10g:

select replace(
         replace(
           replace(
             xmlagg(xmlelement("x",object_id)).getStringVal(),
             '</x><x>',
             ','
           ),
           '<x>',
           ''
         ),
         '</x>',
         ''
       ) id_string
from cr_object_group_entries_vw

附言我不知道在哪个 Oracle 版本 sys_connect_by_path 和 xmlagg 中被引入，但两者都在 10.2.0.4.0 上运行良好

P.S. I didn't know exactly in which Oracle versions sys_connect_by_path and xmlagg was introduced, but both works well on 10.2.0.4.0

相关文章