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

客官°小女子只卖身不卖艺 2022-01-14 00:07 470阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

查询的语句为：  
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

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/40695eb35b0443668e1a65457367c138.png