我原先的sql:
SELECT ut.userid AS USERID,
ut.operatorname AS USERNAME,
ut.operatorid AS OPERATORID,
upt1.posicode AS POSICODES,
ot.orgseqname AS ORGDEPT,
ot.orgname AS OFFICE
FROM bpm_user ut
RIGHT JOIN (select upt.operatorid as operatorid,
to_char(substr(WM_CONCAT(upt.posicode), 1, 4000)) as posicode
from bpm_user_position upt
where upt.flag = 'Y'
group by upt.operatorid) upt1 ON upt1.operatorid =
ut.operatorid
INNER JOIN bpm_org ot ON ot.orgid = ut.orgid
where 1 = 1
AND upt1.posicode like '%10008%';
这段sql在数据库执行没有问题,但是在程序一直报错;
然后分析问题发现在WM_CONCAT 之后在to_char的时候就会报错,如果不to_char 返回的数据又是clob的路径;
最后小小的修改了下,卧槽 问题就解决了! 但是具体原因 我也不明白,有知道的大神可以告诉我下吗?
修改后的代码:
SELECT ut.userid AS USERID,
ut.operatorname AS USERNAME,
ut.operatorid AS OPERATORID,
to_char(substr(upt1.posicode, 1, 4000)) AS POSICODES,
ot.orgseqname AS ORGDEPT,
ot.orgname AS OFFICE
FROM bpm_user ut
RIGHT JOIN (select upt.operatorid as operatorid,
WM_CONCAT(upt.posicode) as posicode
from bpm_user_position upt
where upt.flag = 'Y'
group by upt.operatorid) upt1 ON upt1.operatorid =
ut.operatorid
INNER JOIN bpm_org ot ON ot.orgid = ut.orgid
where 1 = 1
AND upt1.posicode like '%10008%';
网上查了各种资料还是没找到能解决我问题的,最后自己解决了 哈哈! 记录下以便有遇到相同的问题的人能有个参考!