Oracle中wm_concat函数报错解决方法

Oracle中wm_concat函数报错解决方法

 

原SQL语句

 

select t.mail_no,wm_concat(t.notice_no) as notice_no_all from ardba.arbino t group by t.mail_no

错误信息

 

 

解决SQL

 

select a.mail_no,wm_concat(a.notice_no) || max(a.str) as notice_no_all from 

(select k.mail_no,k.notice_no, 

case when k.all_sum > 4000 then '...' else null end as str from

(select t.mail_no, t.notice_no, 

sum(vsize(t.notice_no || ',')) over(partition by t.mail_no) as all_sum, 

sum(vsize(t.notice_no || ',')) over(partition by t.mail_no order by t.notice_no) as up_sum 

from ardba.arbino t) k

where (k.up_sum <= 3998 and k.all_sum > 4000) 

or k.all_sum <= 4001) a

group by a.mail_no

 

最终效果图