刷新数据时KSQL报错

qweasdzxc · 发表于 2017-12-12 11:50:35

本帖最后由 qweasdzxc 于 2017-12-12 12:11 编辑

2017-12-12 11:39:02 excute stream action, params:{"accept":"application/json","action":"queryV_SA_OPOrg_WQUAction","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"columns":"DeptCode,DeptName,IfPwd,PsnClass,V_SA_OPOrg_WQU,sAddress,sCode,sDescription,sFCode,sFID,sFName,sFax,sFromHR,sID,sLevel,sLongName,sName,sNodeKind,sOrgKindID,sParentS,sPersonID,sPhone,sSequence,sValidState,sWxDeptID,sZip,version","filter":"((SQL.patindex(concat ('/1001.ogn/1001A1100000000000DF.dpt/1001A110000000006J9Y.pos/1001A110000000000BF0@1001A110000000006J9Y.psm','%'),sFID)>0) and 1=1 and (PsnClass in ('职员','职工','海外人员','特殊人员')) and ((SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000FI07S.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A1100000000000DF.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009W.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000D79KG.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A1100000000000DF.dpt/1001A1100000000D8MC1.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009T.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000002FXQT.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000003NE80.dpt','%'),sFID)>0)))","limit":20,"offset":0,"variables":{}},"process":"/HR/HR005/process/SecondaryManagement/secondaryManagementProcess","translateParameter":{"cellnameByRelation":false,"dataType":"row-list","includeState":true,"rowsConfig":{"concept":"V_SA_OPOrg_WQU","sequence":"DeptCode,DeptName,IfPwd,PsnClass,V_SA_OPOrg_WQU,sAddress,sCode,sDescription,sFCode,sFID,sFName,sFax,sFromHR,sID,sLevel,sLongName,sName,sNodeKind,sOrgKindID,sParentS,sPersonID,sPhone,sSequence,sValidState,sWxDeptID,sZip,version"},"transformIdcolumn":true,"useNamespace":true}}
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at com.justep.system.action.Engine.invokeActions(Native Method)
at com.justep.system.action.Engine.invokeActions(Unknown Source)
at com.justep.business.server.BusinessServer.doAction(Unknown Source)
at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
at com.justep.business.server.BusinessServer.excute(Unknown Source)
at com.justep.business.server.BusinessServer.excute(Unknown Source)
at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT V_SA_OPOrg_WQU.* FROM V_SA_OPOrg_WQU V_SA_OPOrg_WQU WHERE (((SQL.patindex(concat ('/1001.ogn/1001A1100000000000DF.dpt/1001A110000000006J9Y.pos/1001A110000000000BF0@1001A110000000006J9Y.psm','%'),sFID)>0) and 1=1 and (PsnClass in ('职员','职工','海外人员','特殊人员')) and ((SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000FI07S.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A1100000000000DF.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009W.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000D79KG.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A1100000000000DF.dpt/1001A1100000000D8MC1.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009T.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000002FXQT.dpt','%'),sFID)>0) or (SQL.patindex(concat ('/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000003NE80.dpt','%'),sFID)>0)))) LIMIT 0, 20
--> sql: SELECT V_SA_OPOrg_WQU.DEPTCODE AS DeptCode, V_SA_OPOrg_WQU.DEPTNAME AS DeptName, V_SA_OPOrg_WQU.IFPWD AS IfPwd, V_SA_OPOrg_WQU.PSNCLASS AS PsnClass, V_SA_OPOrg_WQU.SID AS V_SA_OPOrg_WQU, V_SA_OPOrg_WQU.SADDRESS AS sAddress, V_SA_OPOrg_WQU.SCODE AS sCode, V_SA_OPOrg_WQU.SDESCRIPTION AS sDescription, V_SA_OPOrg_WQU.SFCODE AS sFCode, V_SA_OPOrg_WQU.SFID AS sFID, V_SA_OPOrg_WQU.SFNAME AS sFName, V_SA_OPOrg_WQU.SFAX AS sFax, V_SA_OPOrg_WQU.SFROMHR AS sFromHR, V_SA_OPOrg_WQU.SID AS sID, V_SA_OPOrg_WQU.SLEVEL AS sLevel, V_SA_OPOrg_WQU.SLONGNAME AS sLongName, V_SA_OPOrg_WQU.SNAME AS sName, V_SA_OPOrg_WQU.SNODEKIND AS sNodeKind, V_SA_OPOrg_WQU.SORGKINDID AS sOrgKindID, V_SA_OPOrg_WQU.SPARENTS AS sParentS, V_SA_OPOrg_WQU.SPERSONID AS sPersonID, V_SA_OPOrg_WQU.SPHONE AS sPhone, V_SA_OPOrg_WQU.SSEQUENCE AS sSequence, V_SA_OPOrg_WQU.SVALIDSTATE AS sValidState, V_SA_OPOrg_WQU.SWXDEPTID AS sWxDeptID, V_SA_OPOrg_WQU.SZIP AS sZip, V_SA_OPOrg_WQU.VERSION AS version FROM V_SA_OPOrg_WQU V_SA_OPOrg_WQU WHERE (patindex(? + ?, sFID) > ?) AND (? = ?) AND ( PsnClass IN (?, ?, ?, ?) ) AND ( (patindex(? + ?, sFID) > ?) OR (patindex(? + ?, sFID) > ?) OR (patindex(? + ?, sFID) > ?) OR (patindex(? + ?, sFID) > ?) OR (patindex(? + ?, sFID) > ?) OR (patindex(? + ?, sFID) > ?) OR (patindex(? + ?, sFID) > ?) OR (patindex(? + ?, sFID) > ?) )
--> binds: [/1001.ogn/1001A1100000000000DF.dpt/1001A110000000006J9Y.pos/1001A110000000000BF0@1001A110000000006J9Y.psm, %, 0, 1, 1, 职员, 职工, 海外人员, 特殊人员, /1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000FI07S.dpt, %, 0, /1001.ogn/1001A1100000000000DF.dpt, %, 0, /1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009W.dpt, %, 0, /1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000D79KG.dpt, %, 0, /1001.ogn/1001A1100000000000DF.dpt/1001A1100000000D8MC1.dpt, %, 0, /1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009T.dpt, %, 0, /1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000002FXQT.dpt, %, 0, /1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000003NE80.dpt, %, 0]
at com.justep.exception.BusinessException.create(Unknown Source)
at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
at com.justep.system.data.BizData.query(Unknown Source)
at BizProcedure.query(BizProcedure.java:12)
... 27 more
Caused by: java.sql.SQLException: The value supplied cannot be converted to INTEGER.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:570)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getInt(JtdsResultSet.java:657)
at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:225)
at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:225)
at com.justep.system.data.impl.TableUtilsImplement.fillRow(Unknown Source)
... 32 more

复制代码

数据库里能正常查询到数据，条件的字段类型都是string,没有INTEGER的

jishuang · 发表于 2017-12-12 13:31:50

SELECT
V_SA_OPOrg_WQU.DEPTCODE AS DeptCode
,V_SA_OPOrg_WQU.DEPTNAME AS DeptName
,V_SA_OPOrg_WQU.IFPWD AS IfPwd
,V_SA_OPOrg_WQU.PSNCLASS AS PsnClass
,V_SA_OPOrg_WQU.SID AS V_SA_OPOrg_WQU
,V_SA_OPOrg_WQU.SADDRESS AS sAddress
,V_SA_OPOrg_WQU.SCODE AS sCode
,V_SA_OPOrg_WQU.SDESCRIPTION AS sDescription
,V_SA_OPOrg_WQU.SFCODE AS sFCode
,V_SA_OPOrg_WQU.SFID AS sFID
,V_SA_OPOrg_WQU.SFNAME AS sFName
,V_SA_OPOrg_WQU.SFAX AS sFax
,V_SA_OPOrg_WQU.SFROMHR AS sFromHR
,V_SA_OPOrg_WQU.SID AS sID
,V_SA_OPOrg_WQU.SLEVEL AS sLevel
,V_SA_OPOrg_WQU.SLONGNAME AS sLongName
,V_SA_OPOrg_WQU.SNAME AS sName
,V_SA_OPOrg_WQU.SNODEKIND AS sNodeKind
,V_SA_OPOrg_WQU.SORGKINDID AS sOrgKindID
,V_SA_OPOrg_WQU.SPARENTS AS sParentS
,V_SA_OPOrg_WQU.SPERSONID AS sPersonID
,V_SA_OPOrg_WQU.SPHONE AS sPhone
,V_SA_OPOrg_WQU.SSEQUENCE AS sSequence
,V_SA_OPOrg_WQU.SVALIDSTATE AS sValidState
,V_SA_OPOrg_WQU.SWXDEPTID AS sWxDeptID
,V_SA_OPOrg_WQU.SZIP AS sZip
,V_SA_OPOrg_WQU.VERSION AS version
FROM
V_SA_OPOrg_WQU V_SA_OPOrg_WQU
WHERE
(patindex(
'/1001.ogn/1001A1100000000000DF.dpt/1001A110000000006J9Y.pos/1001A110000000000BF0@1001A110000000006J9Y.psm' + '%'
,sFID
) > 0)
AND (1 = 1)
AND (
PsnClass IN (
'职员'
,'职工'
,'海外人员'
,'特殊人员'
)
)
AND (
(patindex(
'/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000FI07S.dpt' + '%'
,sFID
) > 0)
OR (patindex(
'/1001.ogn/1001A1100000000000DF.dpt'+ '%'
,sFID
) > 0)
OR (patindex(
'/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009W.dpt' + '%'
,sFID
) > 0)
OR (patindex(
'/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A1100000000D79KG.dpt' + '%'
,sFID
) > 0)
OR (patindex(
'/1001.ogn/1001A1100000000000DF.dpt/1001A1100000000D8MC1.dpt' + '%'
,sFID
) > 0)
OR (patindex(
'/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000000009T.dpt' + '%'
,sFID
) > 0)
OR (patindex(
'/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000002FXQT.dpt' + '%'
,sFID
) > 0)
OR (patindex(
'/1001.ogn/1001A11000000000009R.dpt/1001A11000000000009S.dpt/1001A11000000003NE80.dpt' + '%'
,sFID
) > 0)
);

复制代码

到数据库执行没问题?

qweasdzxc · 发表于 2017-12-12 14:10:23

本帖最后由 qweasdzxc 于 2017-12-12 14:20 编辑

jishuang 发表于 2017-12-12 13:31
到数据库执行没问题?

X5是3.6版本数据库是SQL Server
数据库运行没问题能查询出想要的数据

jishuang · 发表于 2017-12-12 15:17:32

确认概念中的关系类型和数据库中都一致？

		自动登录	找回密码
密码			注册

[结贴] 刷新数据时KSQL报错