执行sql:
SELECT
A.LNGBUDGETMODELID,
A.LNGBUDGETMODELDETAILCID,
A.LNGBUDGETPROJECTID,
A.LNGRESIDEDEPARTMENTID,
A.LNGDEPARTMENTID,
A.LNGFUNDSOURCEID,
A.STRBUDGETPROJECTCODEANDNAME,
A.STRRESIDEDEPARTMENTCODEANDNAME,
A.STRFUNDSOURCENAME,
RTRIM(XMLAGG(
CASE
WHEN A.LNGOPERATORID1005 IS NOT NULL THEN
XMLPARSE(CONTENT A.LNGOPERATORID1005 || ',' WELLFORMED)
END ORDER BY A.LNGOPERATORID1005).GETCLOBVAL(), ',') AS LNGOPERATORID1005,
RTRIM(XMLAGG(
CASE
WHEN A.STROPERATORNAME1005 IS NOT NULL THEN
XMLPARSE(CONTENT to_char(A.STROPERATORNAME1005) || ',' WELLFORMED)
END ORDER BY to_char(A.STROPERATORNAME1005)).GETCLOBVAL(), ',') AS STROPERATORNAME1005,
RTRIM(XMLAGG(
CASE
WHEN A.LNGOPERATORID1006 IS NOT NULL THEN
XMLPARSE(CONTENT A.LNGOPERATORID1006 || ',' WELLFORMED)
END ORDER BY A.LNGOPERATORID1006).GETCLOBVAL(), ',') AS LNGOPERATORID1006,
RTRIM(XMLAGG(
CASE
WHEN A.STROPERATORNAME1006 IS NOT NULL THEN
XMLPARSE(CONTENT to_char(A.STROPERATORNAME1006) || ',' WELLFORMED)
END ORDER BY to_char(A.STROPERATORNAME1006)).GETCLOBVAL(), ',') AS STROPERATORNAME1006,
RTRIM(XMLAGG(
CASE
WHEN A.LNGOPERATORID7 IS NOT NULL THEN
XMLPARSE(CONTENT A.LNGOPERATORID7 || ',' WELLFORMED)
END ORDER BY A.LNGOPERATORID7).GETCLOBVAL(), ',') AS LNGOPERATORID7,
RTRIM(XMLAGG(
CASE
WHEN A.STROPERATORNAME7 IS NOT NULL THEN
XMLPARSE(CONTENT to_char(A.STROPERATORNAME7) || ',' WELLFORMED)
END ORDER BY to_char(A.STROPERATORNAME7)).GETCLOBVAL(), ',') AS STROPERATORNAME7,
RTRIM(XMLAGG(
CASE
WHEN A.LNGOPERATORID8 IS NOT NULL THEN
XMLPARSE(CONTENT A.LNGOPERATORID8 || ',' WELLFORMED)
END ORDER BY A.LNGOPERATORID8).GETCLOBVAL(), ',') AS LNGOPERATORID8,
RTRIM(XMLAGG(
CASE
WHEN A.STROPERATORNAME8 IS NOT NULL THEN
XMLPARSE(CONTENT to_char(A.STROPERATORNAME8) || ',' WELLFORMED)
END ORDER BY to_char(A.STROPERATORNAME8)).GETCLOBVAL(), ',') AS STROPERATORNAME8,
RTRIM(XMLAGG(
CASE
WHEN A.LNGOPERATORID9 IS NOT NULL THEN
XMLPARSE(CONTENT A.LNGOPERATORID9 || ',' WELLFORMED)
END ORDER BY A.LNGOPERATORID9).GETCLOBVAL(), ',') AS LNGOPERATORID9,
RTRIM(XMLAGG(
CASE
WHEN A.STROPERATORNAME9 IS NOT NULL THEN
XMLPARSE(CONTENT to_char(A.STROPERATORNAME9) || ',' WELLFORMED)
END ORDER BY to_char(A.STROPERATORNAME9)).GETCLOBVAL(), ',') AS STROPERATORNAME9,
RTRIM(XMLAGG(
CASE
WHEN A.LNGOPERATORID1015 IS NOT NULL THEN
XMLPARSE(CONTENT A.LNGOPERATORID1015 || ',' WELLFORMED)
END ORDER BY A.LNGOPERATORID1015).GETCLOBVAL(), ',') AS LNGOPERATORID1015,
RTRIM(XMLAGG(
CASE
WHEN A.STROPERATORNAME1015 IS NOT NULL THEN
XMLPARSE(CONTENT to_char(A.STROPERATORNAME1015) || ',' WELLFORMED)
END ORDER BY to_char(A.STROPERATORNAME1015)).GETCLOBVAL(), ',') AS STROPERATORNAME1015,
A.STRDEPARTMENTCODEANDNAME
FROM
(SELECT DISTINCT
MD.LNGBUDGETMODELID,
MD.LNGBUDGETMODELDETAILCID,
MD.LNGBUDGETPROJECTID,
MD.LNGRESIDEDEPARTMENTID,
MD.LNGDEPARTMENTID,
MD.LNGFUNDSOURCEID,
P.STRBUDGETPROJECTCODE || ' ' || P.STRBUDGETPROJECTNAME STRBUDGETPROJECTCODEANDNAME,
GK.STRDEPARTMENTCODE || ' ' || GK.STRDEPARTMENTNAME STRRESIDEDEPARTMENTCODEANDNAME,
F.STRFUNDSOURCECODE,
F.STRFUNDSOURCENAME,
DECODE(R.LNGFLOWSETID, 1005, O.LNGOPERATORID, '') LNGOPERATORID1005,
DECODE(R.LNGFLOWSETID, 1005, O.STROPERATORCODE || ' ' || O.STROPERATORNAME, '') STROPERATORNAME1005,
DECODE(R.LNGFLOWSETID, 1006, O.LNGOPERATORID, '') LNGOPERATORID1006,
DECODE(R.LNGFLOWSETID, 1006, O.STROPERATORCODE || ' ' || O.STROPERATORNAME, '') STROPERATORNAME1006,
DECODE(R.LNGFLOWSETID, 7, O.LNGOPERATORID, '') LNGOPERATORID7,
DECODE(R.LNGFLOWSETID, 7, O.STROPERATORCODE || ' ' || O.STROPERATORNAME, '') STROPERATORNAME7,
DECODE(R.LNGFLOWSETID, 8, O.LNGOPERATORID, '') LNGOPERATORID8,
DECODE(R.LNGFLOWSETID, 8, O.STROPERATORCODE || ' ' || O.STROPERATORNAME, '') STROPERATORNAME8,
DECODE(R.LNGFLOWSETID, 9, O.LNGOPERATORID, '') LNGOPERATORID9,
DECODE(R.LNGFLOWSETID, 9, O.STROPERATORCODE || ' ' || O.STROPERATORNAME, '') STROPERATORNAME9,
DECODE(R.LNGFLOWSETID, 1015, O.LNGOPERATORID, '') LNGOPERATORID1015,
DECODE(R.LNGFLOWSETID, 1015, O.STROPERATORCODE || ' ' || O.STROPERATORNAME, '') STROPERATORNAME1015,
D.STRDEPARTMENTCODE || ' ' || D.STRDEPARTMENTNAME STRDEPARTMENTCODEANDNAME
FROM
BUDGETMODELDETAIL MD
INNER JOIN BUDGETPROJECT P ON MD.LNGBUDGETPROJECTID = P.LNGBUDGETPROJECTID
INNER JOIN XLYKCB0001.DEPARTMENT GK ON MD.LNGRESIDEDEPARTMENTID = GK.LNGDEPARTMENTID
INNER JOIN XLYKCB0001.DEPARTMENT D ON MD.LNGDEPARTMENTID = D.LNGDEPARTMENTID
INNER JOIN XLYKCB0001.FUNDSOURCE F ON MD.LNGFUNDSOURCEID = F.LNGFUNDSOURCEID
LEFT JOIN BUDGETMODELDETAILROLE R ON MD.LNGBUDGETMODELDETAILCID = R.LNGBUDGETMODELDETAILCID
LEFT JOIN XLYKCB0001.OPERATOR O ON R.LNGOPERATORID = O.LNGOPERATORID
WHERE
MD.BLNISVOID = 0
AND MD.BLNABOLISH = 0
AND MD.LNGBUDGETMODELID = 9) A
GROUP BY
A.LNGBUDGETMODELID,
A.LNGBUDGETMODELDETAILCID,
A.LNGBUDGETPROJECTID,
A.LNGRESIDEDEPARTMENTID,
A.LNGDEPARTMENTID,
A.LNGFUNDSOURCEID,
A.STRBUDGETPROJECTCODEANDNAME,
A.STRRESIDEDEPARTMENTCODEANDNAME,
A.STRDEPARTMENTCODEANDNAME,
A.STRFUNDSOURCECODE,
A.STRFUNDSOURCENAME
ORDER BY
A.STRBUDGETPROJECTCODEANDNAME,
A.STRRESIDEDEPARTMENTCODEANDNAME,
A.STRDEPARTMENTCODEANDNAME,
A.STRFUNDSOURCECODE
执行失败的dm8版本:
执行成功的dm8版本:
执行失败时的错误提示信息:
执行失败,失败原因:
[-5403]:第125行附近出现错误:
参数不兼容
执行完成
这个是版本的问题吗?还是说是参数设置上两个环境有不相同的地方,这里出现的问题应该检查什么参数呢?
做个试验,只是个试验。
在查询开头,SELECT后,加一个HINT /+ CASE_WHEN_CVT_IFUN(8)/,类似于
SELECT /*+ CASE_WHEN_CVT_IFUN(8)*/
A.LNGBUDGETMODELID,
A.LNGBUDGETMODELDETAILCID,
......
然后在之前有报异常的缓存下测试,看看是否能正常了。
这个HINT的目的是不让CASE表达式换成内部函数处理方式,也就是条件判断通过后才调用XMLPARSE。
先作一下排除法吧。
看着应该时达梦数据库兼容参数的问题,查询下COMPATIBLE_MODE,调整成COMPATIBLE_MODE=2(需要重启数据库服务生效)兼容oracle模式测试下select * from v$dm_ini where para_name='COMPATIBLE_MODE'

两个数据库环境COMPATIBLE_MODE参数分别是多少,SQL语句整体看来没什么问题,肯定和数据库参数有关