为什么wm_concat在这里不起作用? [英] Why does the wm_concat not work here?

问题描述

我有这个查询:

(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提琴中.

解决方法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

P.S.我不知道到底是在哪个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

这篇关于为什么wm_concat在这里不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！