设置有互逆关系的主子表，关联查询时报错

clumrays

Bex5_v3.2如题：
主表：CostReimburse ，子表：CostReimburseDetail ，
其中：CostReimburseDetail .fBXZBID =CostReimburse （主从关联关系），互逆字段CostReimburse .fBXMX
说明，互逆字段fBXMX在mapping中已添加了映射关系

KSQL定义：select sum(crd.fJE) as fSJSSFY_BX_CG from CostReimburse cr JOIN CostReimburseDetail crd ON cr = crd.fBXZBID


执行报错：
SELECT  SUM(crd.FJE) AS fSJSSFY_BX_CG FROM CostReimburse cr JOIN (CostReimburseDetail crd LEFT JOIN CostReimburse CostReimburse__1 ON crd.fID = CostReimburse__1.fBXMX  ) ON cr.fID = CostReimburse__1.fID, binds: []出错
个人觉得这样是因为：因为互逆的关系，生成了嵌套的Join的问题


请问：这样的问题如何解决？

jishuang

把详细的报错信息发一下

CostReimburse .fBXMX这个是不需要在数据库中存储的，所以不用在mapping中添加了映射关系
把CostReimburseDetail .fBXZBID在mapping中添加了映射关系

clumrays

jishuang 发表于 2015-10-12 09:50
把详细的报错信息发一下

CostReimburse .fBXMX这个是不需要在数据库中存储的，所以不用在mapping中添加了 ...

不添加映射的话，就会报下面的错。

2015-10-12 11:29:39 excute stream action, params:{"accept":"application/json","action":"insertProjectEndTables","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"projectID":"9DF65CEF4E864F83AC8ED3FF4FA3677B","sData1":"C6CC2C5366E00001B96FC2606105A700"},"process":"/jrentekOA/BusinessApplication/ProjectMgmt/ProjectDoPlan/process/ProjectDoOverview/projectDoOverviewProcess"}
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        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 sum(cr.fZJE) as fSJSSFY_BX from CostReimburse cr JOIN V_sa_task task ON cr = task.sData1 and task.sParentID is null and task.sStatusID='tesFinished' where cr.fXM = '9DF65CEF4E864F83AC8ED3FF4FA3677B' and cr.fBXLX='785A9AAE283843B78B069087C6C340CC'
--> sql:
--> binds: []
        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.KSQL.select(Unknown Source)
        at ProjectDoPlan.insertProjectEndTables(ProjectDoPlan.java:112)
        ... 27 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP154009; 提示: 互逆关系CostReimburse.fBXMX <-> CostReimburseDetail.fBXZBID, 必须有一个定义映射
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.util.Utils.check(Unknown Source)
        at com.justep.system.ksql.ConceptMapping.fillDefaultMapping(Unknown Source)
        at com.justep.system.ksql.ConceptMapping.getConceptMapping(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.confirmSymTable(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
        ... 32 more

clumrays

jishuang 发表于 2015-10-12 09:50
把详细的报错信息发一下

CostReimburse .fBXMX这个是不需要在数据库中存储的，所以不用在mapping中添加了 ...

补充说明下：不是建这个表的本身业务，是在另外一个业务引用这个表的。

clumrays

jishuang 发表于 2015-10-12 09:50
把详细的报错信息发一下

CostReimburse .fBXMX这个是不需要在数据库中存储的，所以不用在mapping中添加了 ...

设置映射后，就报这个错。

2015-10-12 12:29:55 excute stream action, params:{"accept":"application/json","action":"insertProjectEndTables","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"projectID":"9DF65CEF4E864F83AC8ED3FF4FA3677B","sData1":"C6CC2FD0674000018BDA9AD21FD1E860"},"process":"/jrentekOA/BusinessApplication/ProjectMgmt/ProjectDoPlan/process/ProjectDoOverview/projectDoOverviewProcess"}
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        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 sum(crd.fJE) as fSJSSFY_BX_CG from CostReimburse cr JOIN CostReimburseDetail crd ON cr = crd.fBXZBID and crd.fFYLX = 'DB3FAE1F79244FC9ABB66A0BBACD112A' where cr.fXM = '9DF65CEF4E864F83AC8ED3FF4FA3677B' and cr.fBXLX='785A9AAE283843B78B069087C6C340CC'
--> sql: SELECT  SUM(crd.FJE) AS fSJSSFY_BX_CG FROM CostReimburse cr JOIN (CostReimburseDetail crd LEFT JOIN CostReimburse CostReimburse__1 ON crd.fID = CostReimburse__1.fBXMX  ) ON  (cr.fID = CostReimburse__1.fID) AND (crd.FFYLX = ?)   WHERE  (cr.FXM = ?) AND (cr.FBXLX = ?)
--> binds: [DB3FAE1F79244FC9ABB66A0BBACD112A, 9DF65CEF4E864F83AC8ED3FF4FA3677B, 785A9AAE283843B78B069087C6C340CC]
        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.KSQL.select(Unknown Source)
        at ProjectDoPlan.insertProjectEndTables(ProjectDoPlan.java:122)
        ... 27 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: SELECT  SUM(crd.FJE) AS fSJSSFY_BX_CG FROM CostReimburse cr JOIN (CostReimburseDetail crd LEFT JOIN CostReimburse CostReimburse__1 ON crd.fID = CostReimburse__1.fBXMX  ) ON  (cr.fID = CostReimburse__1.fID) AND (crd.FFYLX = ?)   WHERE  (cr.FXM = ?) AND (cr.FBXLX = ?) , binds: [DB3FAE1F79244FC9ABB66A0BBACD112A, 9DF65CEF4E864F83AC8ED3FF4FA3677B, 785A9AAE283843B78B069087C6C340CC]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlSelect(Unknown Source)
        ... 32 more
Caused by: java.sql.SQLException: Unknown column 'CostReimburse__1.fBXMX' in 'on clause'
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        ... 33 more

jishuang

CostReimburse .fBXMX这个是不需要在数据库中存储的，所以不用在mapping中添加了映射关系
把CostReimburseDetail .fBXZBID在mapping中添加了映射关系

clumrays

jishuang 发表于 2015-10-12 17:27
CostReimburse .fBXMX这个是不需要在数据库中存储的，所以不用在mapping中添加了映射关系
把CostReimburseD ...

这下明白了，谢谢。