费了一晚上才解决的oracle数据库提示ORA-01489字符串连接的结果过长

likeyouli

首先说明，字符串肯定没有过长，最多估计都不到100，但递归查询时就是提示错误，见图，代码如下：with jichu as (select shfzhm,yyxmmc,tcje,fyfssj,row_number() over(partition by shfzhm order by fyfssj) as rn from tanghua  where ybxmmc like '%糖化血红蛋白%') ,
digui (shfzhm,yyxmmc,tcje,fyfssj,rn,所有日期) as (
select jichu.*,to_char(jichu.fyfssj,'yyyy-mm-dd') as 所有日期 from jichu  where rn=1
union all
select jichu.shfzhm,jichu.yyxmmc,jichu.tcje,jichu.fyfssj,jichu.rn,  (to_char(jichu.fyfssj,'yyyy-mm-dd') || '>>' ||所有日期)    as 所有日期
from jichu join digui on jichu.shfzhm=digui.shfzhm and jichu.rn=digui.rn+1
)
select * from digui

解决方式见二楼，，高手误嘲笑，实在费了洋劲才解决的，各种百度，deepseek都白搭，偶然测试才发现解决方式，看样子我对oracle数据库的底层了解太浅了，到现在都没明白咋回事，估计to_char转换完是不是有字节大小限制啊, 算了,以后慢慢思考原理.  现心情舒畅的必须向无忧网友报告一下，有遇到同样问题的可参考解决，

likeyouli

解决代码:with jichu as (select shfzhm,yyxmmc,tcje,fyfssj,row_number() over(partition by shfzhm order by fyfssj) as rn from tanghua  where ybxmmc like '%糖化血红蛋白%') ,
digui (shfzhm,yyxmmc,tcje,fyfssj,rn,所有日期) as (
select jichu.*,cast(to_char(jichu.fyfssj,'yyyy-mm-dd') as varchar2(4000)) as 所有日期 from jichu  where rn=1
union all
select jichu.shfzhm,jichu.yyxmmc,jichu.tcje,jichu.fyfssj,jichu.rn,  (to_char(jichu.fyfssj,'yyyy-mm-dd') || '>>' ||所有日期)    as 所有日期
from jichu join digui on jichu.shfzhm=digui.shfzhm and jichu.rn=digui.rn+1
)
select * from digui order by shfzhm,fyfssj

即 to_char转换为字符串后,再cast一下所有日期这列才可以, 感觉所有日期这列有点相当于变量, cast as varchar2(4000) 这样就没问题了, 这么简单的问题, 为啥百度,deepseek就解决不了呢?
是问的方式不对,还是怎么回事  ?? 老让我转to_clob,转完又报什么错误, 关键是根本就不需要转,最长的字符也不长  .
    再说句, 这两天光研究递归查询了, 总算有点明白了  
-------------------------------------------------------------------------------------------------------------
又研究了, 貌似不用cast也可以不出问题,见引用  :

--再练习一下我最讨厌的递归查询,先练习糖化血红蛋白小于90天违规的问题吧
with jichu as (select e2.jshid,e2.shfzhm,e2.xm,yyxmmc,zje,tcje,fyfssj,row_number() over(partition by e2.shfzhm order by fyfssj) rn
from eryuan join yb_jszd_ws e2 on eryuan.jshid=e2.jshid where yyxmmc like '糖化血红蛋白%'),
digui(jshid,shfzhm,xm,yyxmmc,zje,tcje,fyfssj,rn,canzhaoriqi,beizhu,违规金额,所有日期) as (
select jichu.*,fyfssj as canzhaoriqi,'首次' as beizhu,0 as 违规金额,
to_char(fyfssj,'yyyy-mm-dd')  as 所有日期 from jichu where rn=1
union all
select jichu.jshid,jichu.shfzhm,jichu.xm,jichu.yyxmmc,jichu.zje,jichu.tcje,jichu.fyfssj,jichu.rn,
case when jichu.fyfssj-digui.canzhaoriqi>90 then jichu.fyfssj else digui.canzhaoriqi end as canzhaoriqi,
case when jichu.fyfssj-digui.canzhaoriqi>90 then '新的首次' else '非首次非新的首次' end as beizhu,
case when jichu.fyfssj-digui.canzhaoriqi>90 then 0 else jichu.tcje end as 违规金额,
  (to_char(jichu.fyfssj,'yyyy-mm-dd') || '>>' || digui.所有日期) as 所有日期 from jichu
join digui on jichu.shfzhm=digui.shfzhm and jichu.rn=digui.rn+1
)
select digui.*,fyfssj-canzhaoriqi 相差几天 from digui order by shfzhm,rn

小灰兔

谢谢楼主分享

tanglf

谢谢分享经验

旭东

字都认识
膜拜了

chinaren12

这是医院的数据库？？？楼主666啊，无忧真是藏龙卧虎啊。。

yc2428

谢谢分享

lxgood

谢谢楼主分享

guong

感谢分享

86933924

感谢分享

lsm3323203

很不错，下载试试看。。。

lbw2007

to_char有长度限制的，递归拼接尽量不要频繁使用。如果用PL/SQL建议DBMS_OUTPUT.PUT_LINE