为什么在oracle数据库中能执行的SQL，到了nutz框架中会报不是 GROUP BY 表达式

2017-11-13 15:10:29,989 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select max(prop_value) max,round(avg(prop_value)) avg, min(prop_value) min from T_CUSTOMER_REF_PROP t where prop_name = 'heartRate' and ref_id = 1 and '2017-11'=to_char(create_time, 'yyyy-mm')
2017-11-13 15:10:30,013 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select trunc(avg(prop_value)) prop_value,to_char(create_time, 'yyyy-mm-dd') create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11'=to_char(create_time, 'yyyy-mm') group by to_char(create_time, 'yyyy-mm-dd')
2017-11-13 15:10:30,022 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQLException
java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:509)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2017-11-13 15:10:30,023 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQL NextException
2017-11-13 15:11:02,389 [http-bio-8080-exec-6] ERROR com.jiedu.wttp.action.RestAction - 获取心率异常
org.nutz.dao.DaoException: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, 'yyyy-mm-dd') create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11'=to_char(create_time, 'yyyy-mm') group by to_char(create_time, 'yyyy-mm-dd')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, ?)'
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:76)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:509)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, 'yyyy-mm-dd') create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11'=to_char(create_time, 'yyyy-mm') group by to_char(create_time, 'yyyy-mm-dd')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, ?)'
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	... 44 more
Caused by: java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	... 46 more

wendal

1楼•2596天前

group by不能带参数吧？最后一个@改成变量试试

qq_00613415 2楼•2596天前

把最后一个@参数改成$变量后，也不行日期就不是字符串类型的

2017-11-13 15:32:09,238 [http-bio-8080-exec-2] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select max(prop_value) max,round(avg(prop_value)) avg, min(prop_value) min from T_CUSTOMER_REF_PROP t where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd')
2017-11-13 15:32:09,257 [http-bio-8080-exec-2] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, yyyy-mm-dd hh24:mi:ss)
2017-11-13 15:32:09,261 [http-bio-8080-exec-2] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQLException
java.sql.SQLException: 索引中丢失  IN 或 OUT 参数:: 6
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
	at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1681)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:502)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2017-11-13 15:32:09,270 [http-bio-8080-exec-2] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQL NextException
2017-11-13 15:32:09,273 [http-bio-8080-exec-2] ERROR com.jiedu.wttp.action.RestAction - 获取心率异常
org.nutz.dao.DaoException: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, yyyy-mm-dd hh24:mi:ss)'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, yyyy-mm-dd hh24:mi:ss)'
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:76)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:502)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, yyyy-mm-dd hh24:mi:ss)'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, yyyy-mm-dd hh24:mi:ss)'
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	... 44 more
Caused by: java.sql.SQLException: 索引中丢失  IN 或 OUT 参数:: 6
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
	at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1681)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	... 46 more

wendal

3楼•2596天前

少了单引号

qq_00613415 4楼•2596天前

这个单引号，还需要自己在程序中进行拼接吗？

wendal

5楼•2596天前

在sql里面加上，变量是直接嵌进去的，不做处理

qq_00613415 6楼•2596天前

不行，还是报不是GROUP BY表达式

String sqlData = "select trunc(avg(prop_value)) prop_value,to_char(create_time, @timeType) create_time from T_CUSTOMER_REF_PROP "
				+ "where prop_name = @healthType and ref_id = @refId "
				+ "and @time=to_char(create_time, @dateType) "
				+ "group by to_char(create_time, '$timeType')";
==================================

2017-11-13 15:46:21,594 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select max(prop_value) max,round(avg(prop_value)) avg, min(prop_value) min from T_CUSTOMER_REF_PROP t where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd')
2017-11-13 15:46:21,617 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')
2017-11-13 15:46:21,639 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQLException
java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:502)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2017-11-13 15:46:21,651 [http-bio-8080-exec-6] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQL NextException
2017-11-13 15:46:21,657 [http-bio-8080-exec-6] ERROR com.jiedu.wttp.action.RestAction - 获取心率异常
org.nutz.dao.DaoException: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:76)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:502)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	... 44 more
Caused by: java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	... 46 more

wendal

7楼•2596天前

去掉话说，order by 不需要加asc或者desc吗？

qq_00613415 8楼•2596天前

我没有用order by 啊？

wendal

9楼•2596天前

哦(´-ω-`)，我的锅，看眼花了

qq_00613415 10楼•2596天前

那这个要怎么解决啊

wendal

11楼•2596天前

to_char(create_time, ?) 这些统统改成变量，然后去掉where条件试试

qq_00613415 12楼•2596天前

我的where条件不能去，要带着的，我只把to_char(create_time, ?) 改成变量还是不行

String sqlData = "select trunc(avg(prop_value)) prop_value,to_char(create_time, @timeType) create_time from T_CUSTOMER_REF_PROP "
				+ "where prop_name = @healthType and ref_id = @refId "
				+ "and @time=to_char(create_time, @dateType) "
				+ "group by $timeType";
		Sql sql = Sqls.create(sqlData);
		sql.params().set("refId", refProp.getRefId());
		sql.params().set("time", refProp.getTime());
		sql.params().set("healthType", healthType);
		sql.params().set("dateType", dateType);
		sql.vars().set("timeType", "to_char(create_time, '"+timeType+"')");
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao.getEntity(CustomerRefProp.class));
		dao.execute(sql);
====================================================================
org.nutz.dao.DaoException: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:76)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:502)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd') group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP where prop_name = ? and ref_id = ? and ?=to_char(create_time, ?) group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	... 44 more
Caused by: java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	... 46 more

wendal

13楼•2596天前

把where去掉是为了把问题最小化

wendal

14楼•2596天前

把where条件去掉，把参数通通改成变量，输出的sql应该就没有问号，还是不行的话，那这sql是真的不合法了

qq_00613415 15楼•2596天前

不行

2017-11-13 16:37:37,560 [http-bio-8080-exec-5] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select max(prop_value) max,round(avg(prop_value)) avg, min(prop_value) min from T_CUSTOMER_REF_PROP t where prop_name = 'heartRate' and ref_id = 1 and '2017-11-13'=to_char(create_time, 'yyyy-mm-dd')
2017-11-13 16:37:37,585 [http-bio-8080-exec-5] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')
2017-11-13 16:37:37,607 [http-bio-8080-exec-5] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQLException
java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:502)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2017-11-13 16:37:37,616 [http-bio-8080-exec-5] DEBUG org.nutz.dao.impl.sql.run.NutDaoExecutor - SQL NextException
2017-11-13 16:37:37,626 [http-bio-8080-exec-5] ERROR com.jiedu.wttp.action.RestAction - 获取心率异常
org.nutz.dao.DaoException: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:76)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:182)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthDataList(CustomerRefDao.java:535)
	at com.jiedu.wttp.dao.CustomerRefDao.queryHealthData(CustomerRefDao.java:502)
	at com.jiedu.wttp.action.RestAction.getWatchHeartReat(RestAction.java:960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutServlet.service(NutServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.nutz.dao.DaoException: !Nutz SQL Error: 'select trunc(avg(prop_value)) prop_value,to_char(create_time, NULL) create_time from T_CUSTOMER_REF_PROP group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
PreparedStatement: 
'select trunc(avg(prop_value)) prop_value,to_char(create_time, ?) create_time from T_CUSTOMER_REF_PROP group by to_char(create_time, 'yyyy-mm-dd hh24:mi:ss')'
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	... 44 more
Caused by: java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
	... 46 more

qq_00613415 16楼•2595天前

我把它换成子查询就可以了

String sqlData = "select trunc(avg(prop_value)) prop_value,time from "
				+ "(select prop_value,to_char(create_time, @timeType) time from T_CUSTOMER_REF_PROP "
				+ "where prop_name = @healthType and ref_id = @refId "
				+ "and @time=to_char(create_time, @dateType)) "
				+ "group by time";

请先登陆