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

2019独角兽企业重金招聘Python工程师标准>>>

查询的语句为：
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
重新执行，通过。
结论：结果集里，不能传参数。

转载于:https://my.oschina.net/u/2552902/blog/543869