group by问题

dh_n

select   b.fOrgName,SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') as times,( sum(SQL.nvl(a.fActualHour,0))+ sum(SQL.nvl(a.fExtraHours,0))+ sum(SQL.nvl(a.fNoOrderWorkieHours,0))+ sum(SQL.nvl(a.fAdvanceWorkeHours,0))) as hour  from  ERP_MM_MHCounting a    optional join ERP_MM_ProductionOrder b on a.fProduceOrderID =  b where (SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm')>= SQL.to_Date('2019-06','yyyy-mm')  and  SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') <= SQL.to_Date('2019-06','yyyy-mm'))  and a.fWorkCenterID ='E1730C3B3EAA4B33875CAFE5CCEC81F6'     group by  SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm'),b.fOrgName  order by SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') asc


我在自定义方法写的，执行报错，打开SQL运行日志，没有显示编辑成的SQL语句


select   b.fOrgName,to_date(to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') as times,( sum(nvl(a.fActualHour,0))+ sum(nvl(a.fExtraHours,0))+
sum(nvl(a.fNoOrderWorkieHours,0))+ sum(nvl(a.fAdvanceWorkeHours,0))) as hour
  from  ERP_MM_MHCounting a   
   left join ERP_MM_ProductionOrder b on a.fProduceOrderID =  b.fid
   where (to_date(to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm')>= to_Date('2019-06','yyyy-mm')  
   and  to_date(to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') <= to_Date('2019-06','yyyy-mm'))
    and a.fWorkCenterID ='B214DD681C41489C9D65AADC71999F9F'  
     group by  to_date(to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') , b.fOrgName
order by to_date(to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') asc

这个是根据KSQL改的，在数据库执行没有问题；平台版本Bex5 3.8,数据库oracle

jishuang

报错发具体的错误信息

dh_n

com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: select   b.fOrgName,SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') as times,( sum(SQL.nvl(a.fActualHour,0))+ sum(SQL.nvl(a.fExtraHours,0))+ sum(SQL.nvl(a.fNoOrderWorkieHours,0))+ sum(SQL.nvl(a.fAdvanceWorkeHours,0))) as hour  from  ERP_MM_MHCounting a    optional join ERP_MM_ProductionOrder b on a.fProduceOrderID =  b where (SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm')>= SQL.to_Date('2019-06','yyyy-mm')  and  SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') <= SQL.to_Date('2019-06','yyyy-mm'))  and a.fWorkCenterID ='E1730C3B3EAA4B33875CAFE5CCEC81F6'     group by  SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm'),b.fOrgName  order by SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') asc
--> 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 Statistics.queryWorkCenterOut(Statistics.java:831)
        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 com.justep.micro.service.MicroServiceFilter.doFilter(Unknown Source)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        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.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误, by  SQL.to_date^^(SQL.to_char(a.中未找到需要的元素, 位置632期望是<EOF>
        at com.justep.exception.KSQLException.create(Unknown Source)
        at com.justep.system.ksql.token.TokenManager.matchToken(Unknown Source)
        at com.justep.system.ksql.token.TokenManager.match(Unknown Source)
        at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
        at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
        ... 35 more
2019-06-10 16:57:13 excute stream action, params:{"accept":"application/json","action":"queryWorkCenterOut","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"endDate":"2019-06-29","startDate":"2019-06-10","type":"月","workCenterID":"E1730C3B3EAA4B33875CAFE5CCEC81F6"},"process":"/erp/statistics/process/workCentreOutput/workCentreOutputProcess"}
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 com.justep.micro.service.MicroServiceFilter.doFilter(Unknown Source)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        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: java.lang.RuntimeException
        at Statistics.queryWorkCenterOut(Statistics.java:850)
        ... 30 more

jishuang

order by SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') asc去掉确认执行是否正确

dh_n

com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: select   b.fOrgName,SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') as times,( sum(SQL.nvl(a.fActualHour,0))+ sum(SQL.nvl(a.fExtraHours,0))+ sum(SQL.nvl(a.fNoOrderWorkieHours,0))+ sum(SQL.nvl(a.fAdvanceWorkeHours,0))) as hour  from  ERP_MM_MHCounting a    optional join ERP_MM_ProductionOrder b on a.fProduceOrderID =  b where (SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm')>= SQL.to_Date('2019-06','yyyy-mm')  and  SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm') <= SQL.to_Date('2019-06','yyyy-mm'))  and a.fWorkCenterID ='B214DD681C41489C9D65AADC71999F9F'     group by  SQL.to_date(SQL.to_char(a.fCreateDateTime,'yyyy-mm'),'yyyy-mm'),b.fOrgName  
--> 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 Statistics.queryWorkCenterOut(Statistics.java:831)
        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 com.justep.micro.service.MicroServiceFilter.doFilter(Unknown Source)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        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.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误, by  SQL.to_date^^(SQL.to_char(a.中未找到需要的元素, 位置632期望是<EOF>
        at com.justep.exception.KSQLException.create(Unknown Source)
        at com.justep.system.ksql.token.TokenManager.matchToken(Unknown Source)
        at com.justep.system.ksql.token.TokenManager.match(Unknown Source)
        at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
        at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
        ... 35 more
2019-06-10 17:24:37 excute stream action, params:{"accept":"application/json","action":"queryWorkCenterOut","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"endDate":"2019-06-10","startDate":"2019-06-10","type":"月","workCenterID":"B214DD681C41489C9D65AADC71999F9F"},"process":"/erp/statistics/process/workCentreOutput/workCentreOutputProcess"}
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 com.justep.micro.service.MicroServiceFilter.doFilter(Unknown Source)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        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: java.lang.RuntimeException
        at Statistics.queryWorkCenterOut(Statistics.java:850)
        ... 30 more
不正确

jishuang

那就是KSQL中不能这样用，需要用sql实现

dh_n

KSQL有使用说明吗，哪些可以用，哪些不能用

jishuang

比较复杂的语句都推荐用sql实现，KSQL的使用说明中也是简单的语法说明，stduio的API中就带的有

dh_n

好的，结帖