3

Hi, I have created the following dataset in OQL: SELECT AVG (PU.Average_kVA) as AkVA, DATEPART(DAY,PU/createdDate) as Days FROM Operations."PowerUsage" as PU WHERE PU.ID = $pu AND PU/createdDate IN $dt GROUP BY DATEPART(DAY,PU/createdDate) ORDER BY Days DESC After I've connected it to a report pane and deployed the application, I get the following stacktrace: com.mendix.core.CoreException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"request":"fl (depth = 0): SELECT \tAVG (PU.Average_kVA) as AkVA, \tDATEPART(DAY,PU/createdDate) as Days FROM \tOperations.\"PowerUsage\" as PU WHERE \tPU.ID = $pu AND \tPU/createdDate IN $dt GROUP BY \tDATEPART(MONTH,PU/createdDate) ORDER BY \tDays DESC ","type":"RetrieveOQLDataTableAction"}', all database changes executed by this action were rolled back at hk.b(SourceFile:170) Caused by: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"request":"fl (depth = 0): SELECT \tAVG (PU.Average_kVA) as AkVA, \tDATEPART(DAY,PU/createdDate) as Days FROM \tOperations.\"PowerUsage\" as PU WHERE \tPU.ID = $pu AND \tPU/createdDate IN $dt GROUP BY \tDATEPART(MONTH,PU/createdDate) ORDER BY \tDays DESC ","type":"RetrieveOQLDataTableAction"}', all database changes executed by this action were rolled back at com.mendix.core.actionmanagement.CoreAction.c(SourceFile:493) Caused by: d: An exception has occurred for the following request(s): fl (depth = -1): SELECT AVG (PU.Average_kVA) as AkVA, DATEPART(DAY,PU/createdDate) as Days FROM Operations."PowerUsage" as PU WHERE PU.ID = $pu AND PU/createdDate IN $dt GROUP BY DATEPART(MONTH,PU/createdDate) ORDER BY Days DESC Caused by: d: Exception occurred while retrieving data. (SQL State: 42574, Error Code: -5574) Detail Message: org.hsqldb.HsqlException: expression not in aggregate or GROUP BY columns: EXTRACT(DAY_OF_MONTH FROM PUBLIC.MX_TOLOCALDATETIME("PU"."createddate",'Custom')) Caused by: java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: EXTRACT(DAY_OF_MONTH FROM PUBLIC.MX_TOLOCALDATETIME("PU"."createddate",'Custom')) at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) at w.a(SourceFile:299) at w.a(SourceFile:215) at w.a(SourceFile:165) at w.a(SourceFile:154) at n.a(SourceFile:43) at dt.a(SourceFile:200) at dt.a(SourceFile:158) at dt.a(SourceFile:86) at dx.a(SourceFile:67) at l.a(SourceFile:45) at a.a(SourceFile:249) at gV.a(SourceFile:86) at gV.executeAction(SourceFile:20) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:48) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:435) at hk.b(SourceFile:156) at com.mendix.core.Core.retrieveOQLDataTable(SourceFile:1257) at mY.a(SourceFile:899) at mY.a(SourceFile:751) at mY.a(SourceFile:90) at mY.a(SourceFile:82) at mT.execute(SourceFile:100) at iI.a(SourceFile:311) at iI.a(SourceFile:240) at iI.processRequest(SourceFile:179) at iL.a(SourceFile:71) at com.mendix.core.MxRuntime.processRequest(SourceFile:856) at com.mendix.m2ee.server.handler.RuntimeHandler.handle(RuntimeHandler.java:43) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113) at org.eclipse.jetty.server.Server.handle(Server.java:334) at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:559) at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.java:1007) at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:747) at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:209) at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:406) at org.eclipse.jetty.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:462) at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:436) at java.lang.Thread.run(Thread.java:662) Caused by: org.hsqldb.HsqlException: expression not in aggregate or GROUP BY columns: EXTRACT(DAY_OF_MONTH FROM PUBLIC.MX_TOLOCALDATETIME("PU"."createddate",'Custom')) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.QuerySpecification.resolveGroups(Unknown Source) at org.hsqldb.QuerySpecification.resolveTypesPartTwo(Unknown Source) at org.hsqldb.QueryExpression.resolve(Unknown Source) at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) at org.hsqldb.ParserCommand.compilePart(Unknown Source) at org.hsqldb.ParserCommand.compileStatement(Unknown Source) at org.hsqldb.Session.compileStatement(Unknown Source) at org.hsqldb.StatementManager.compile(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) at w.a(SourceFile:299) at w.a(SourceFile:215) at w.a(SourceFile:165) at w.a(SourceFile:154) at n.a(SourceFile:43) at dt.a(SourceFile:200) at dt.a(SourceFile:158) at dt.a(SourceFile:86) at dx.a(SourceFile:67) at l.a(SourceFile:45) at a.a(SourceFile:249) at gV.a(SourceFile:86) at gV.executeAction(SourceFile:20) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:48) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:435) at hk.b(SourceFile:156) at com.mendix.core.Core.retrieveOQLDataTable(SourceFile:1257) at mY.a(SourceFile:899) at mY.a(SourceFile:751) at mY.a(SourceFile:90) at mY.a(SourceFile:82) at mT.execute(SourceFile:100) at iI.a(SourceFile:311) at iI.a(SourceFile:240) at iI.processRequest(SourceFile:179) at iL.a(SourceFile:71) at com.mendix.core.MxRuntime.processRequest(SourceFile:856) at com.mendix.m2ee.server.handler.RuntimeHandler.handle(RuntimeHandler.java:43) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113) at org.eclipse.jetty.server.Server.handle(Server.java:334) at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:559) at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.java:1007) at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:747) at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:209) at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:406) at org.eclipse.jetty.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:462) at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:436) at java.lang.Thread.run(Thread.java:662) Does anyone know what I'm doing wrong? If I use Month instead of days in my select and group by, I won't get a stack trace.

asked 2012-03-30

1 answers