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

最终效果图

posted @ 2013-04-16 13:18  爱智旮旯  阅读(4486)  评论(0编辑  收藏  举报