视图中字段拼接长度过长，listagg限制4000 varchar 报错

最新推荐文章于 2024-07-09 11:22:10 发布

新时代农民～

最新推荐文章于 2024-07-09 11:22:10 发布

阅读量1.4k

点赞数

文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_38399109/article/details/129839986

版权

在Oracle 查询数据时有时候会需要用到行转列, 使用listagg()函数,但是如果拼接的记录太多会报错.listagg 拼接返回的类型为varchar ,最大长度为4000,当长度过长时会报错, ora-01489 result of String concatenation is too long

left  join
  ( select  a.paper_ as ID,
     as AUTHOR ,
  LISTAGG(base.name,',') WITHIN GROUP(ORDER BY a.order_num_)  as AUTHOR,
  rtrim(extract(xmlagg(xmlelement(e, A.author_,',')),'/E/text()').getclobval(), ',')   as EMP_ID
  from  KY_PAPER_AUTHOR   a
  left join T_STF_BASE base on base.employee_id=a.author_
  group by a.paper_  )   a  on a.id=p.id_      ----全部作者

解决办法：

left  join
  ( select  a.paper_ as ID,
   rtrim(extract(xmlagg(xmlelement(e, base.name,',')),'/E/text()').getclobval(), ',')   as AUTHOR ,
  --LISTAGG(base.name,',') WITHIN GROUP(ORDER BY a.order_num_)  as AUTHOR,
  rtrim(extract(xmlagg(xmlelement(e, A.author_,',')),'/E/text()').getclobval(), ',')   as EMP_ID
  --LISTAGG(a.author_,',') WITHIN GROUP(ORDER BY a.order_num_)  as EMP_ID
  from  KY_PAPER_AUTHOR   a
  left join T_STF_BASE base on base.employee_id=a.author_
  group by a.paper_  )   a  on a.id=p.id_      ----全部作者

使用这种xml的形式性能就差多了