DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.9.78

2015/12/14 11:53
阅读数 260


查询的语句为:
select t.rq,t.pm,coalesce(m.sje,0) sje,coalesce(m.fje,0) fje from ( select b.rq,c.pm from  (values(?),(?),(?),(?),(?) ) b(rq), (values('00010002'),('00020002')) c(pm) ) t left join (select substr(a.LMZJBXX_RQ,1,7) rq,a.LMZJBXX_PM pm,sum(a.LMZJBXX_SJE) sje,sum(a.LMZJBXX_FJE) fje from JCJYXT_HSZM_LMZJBXX a where exists(select 1 from ucap_dept where UNIT_CODE like ? and DEPT_UNID=a.LMZJBXX_JZDW)  group by substr(a.LMZJBXX_RQ,1,7),a.LMZJBXX_PM ) m on t.rq=m.rq and t.pm=m.pm  order by t.rq,t.pm


网友的说法:
Parameter markers can never be used:


    * in a statement that is not a prepared statement
    * in the fullselect of a CREATE VIEW statement
    * in the triggered action of a CREATE TRIGGER statement
    * in a query captured by DB2 Query Patroller 


The statement cannot be processed.


User Response: Correct the syntax of the statement. If untyped parameter markers are not allowed, use the CAST specification to give the parameter marker a data type. 


这是DB2的错误信息中摘出来的。
我觉得是结果集里的“?”出的问题。



因此,将查询语句修改:
select t.rq,t.pm,coalesce(m.sje,0) sje,coalesce(m.fje,0) fje 
from ( select b.rq,c.pm from  (values('2014-09'), ('2014-08'), ('2014-07'), ('2014-06'), ('2014-05') ) b(rq), (values('00010002'),('00020002')) c(pm) ) t left join (select substr(a.LMZJBXX_RQ,1,7) rq,a.LMZJBXX_PM pm,sum(a.LMZJBXX_SJE) sje,sum(a.LMZJBXX_FJE) fje from JCJYXT_HSZM_LMZJBXX a where exists(select 1 from ucap_dept where UNIT_CODE like ? and DEPT_UNID=a.LMZJBXX_JZDW)  group by substr(a.LMZJBXX_RQ,1,7),a.LMZJBXX_PM ) m on t.rq=m.rq and t.pm=m.pm  order by t.rq,t.pm
重新执行,通过。
结论:结果集里,不能传参数。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
在线直播报名
返回顶部
顶部