sql - Why does the wm_concat not work here? -

i have query :

(select object_id cr_object_group_entries_vw object_group_id in     (select item table(cr_fn_split_string('28,56',',')))) 

that returns :

but when :

select wm_concat(object_id)     (select object_id cr_object_group_entries_vw object_group_id in         (select item table(cr_fn_split_string('28,56',',')))) 

i blank result... doing wrong?

you must avoid wm_concat function because undocumented , discovered workaround @ oracle 8i times.

since times of old method custom aggregate function discovered tom kyte here there new workarounds, showed @ examples below.

all of them reproduced in this sql fiddle.

workaround 1 - listagg function, works in 11g:

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

workaround 2 - sys_connect_by_path, works since 10g:

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

workaround 3 - xmlagg, works since 10g:

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

p.s. didn't know in oracle versions sys_connect_by_path , xmlagg introduced, both works on 10.2.0.4.0