OQL - All action were rolled back - Group by

0
  Hi there,  I have the following OQL query on a Data Set:  SELECT Main.Job/Job_Name as Discipline, Main.Candidate.Name as Name, Main.Job/position as Function, Main.Certificate/Name as Certificate, Main.LanguageSkills/Level as English, Main.Candidate/LastWorkedFor as Client, Main.Candidate/Netto as Netto, Main.Candidate.Phone as Phone FROM Main.Candidate Inner join Main.Candidate/Main.Candidate_Job/Main.Job Inner Join Main.Candidate/Main.Candidate_Certificate/Main.Certificate Inner Join Main.Candidate/Main.Candidate_LanguageSkills/Main.LanguageSkills Where Main.Candidate/Level!='employee' and Main.Candidate/Status='Propus' Group by Main.Candidate.Name Which creates an error saying all actions were rolled back. It seems like a basic OQL query and strange is when I replace Group by with Order by it works fine.  The reason for ‘Group by’ is that I want to have all Candidate.Name not repeating.  Maybe I am not using ‘Group by’ correctly. Any suggestions are more than welcomed. Thank you, Also the stack error :  com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"request":"InternalOqlTextGetRequest (depth = 0): \t\t\tSELECT \t\t\tMain.Candidate.Name as Name, \t\t\tMain.Candidate/LastWorkedFor as Client, \t\t\tMain.Candidate/Netto as Netto, \t\t\t \t\t\tMain.Candidate.Phone as Phone \t \t\t\tFROM\t \t\t\tMain.Candidate \t\t\t\t \t\t\tWhere Main.Candidate/Level!='employee' and Main.Candidate/Status='Propus' \t\t\tGroup by Main.Candidate.Name\t\t\t ","type":"RetrieveOQLDataTableAction"}', all database changes executed by this action were rolled back at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:159) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"request":"InternalOqlTextGetRequest (depth = 0): \t\t\tSELECT \t\t\tMain.Candidate.Name as Name, \t\t\tMain.Candidate/LastWorkedFor as Client, \t\t\tMain.Candidate/Netto as Netto, \t\t\t \t\t\tMain.Candidate.Phone as Phone \t \t\t\tFROM\t \t\t\tMain.Candidate \t\t\t\t \t\t\tWhere Main.Candidate/Level!='employee' and Main.Candidate/Status='Propus' \t\t\tGroup by Main.Candidate.Name\t\t\t ","type":"RetrieveOQLDataTableAction"}', all database changes executed by this action were rolled back at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:36) Caused by: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"request":"InternalOqlTextGetRequest (depth = 0): \t\t\tSELECT \t\t\tMain.Candidate.Name as Name, \t\t\tMain.Candidate/LastWorkedFor as Client, \t\t\tMain.Candidate/Netto as Netto, \t\t\t \t\t\tMain.Candidate.Phone as Phone \t \t\t\tFROM\t \t\t\tMain.Candidate \t\t\t\t \t\t\tWhere Main.Candidate/Level!='employee' and Main.Candidate/Status='Propus' \t\t\tGroup by Main.Candidate.Name\t\t\t ","type":"RetrieveOQLDataTableAction"}', all database changes executed by this action were rolled back at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.processErrorState(CoreActionHandlerImpl.scala:160) Caused by: com.mendix.basis.connectionbus.ConnectionBusException: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): SELECT Main.Candidate.Name as Name, Main.Candidate/LastWorkedFor as Client, Main.Candidate/Netto as Netto, Main.Candidate.Phone as Phone FROM Main.Candidate Where Main.Candidate/Level!='employee' and Main.Candidate/Status='Propus' Group by Main.Candidate.Name at com.mendix.connectionbus.RequestAnalyzer.doRequest(RequestAnalyzer.java:55) Caused by: com.mendix.basis.connectionbus.ConnectionBusException: Exception occurred while retrieving data. (SQL State: 42574, Error Code: -5574) Detail Message: org.hsqldb.HsqlException: expression not in aggregate or GROUP BY columns: PUBLIC."main$candidate"."lastworkedfor" at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.getCorrectException(JdbcDataStore.java:736) Caused by: java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: PUBLIC."main$candidate"."lastworkedfor" in statement [SELECT "main$candidate"."name" AS "Name", "main$candidate"."lastworkedfor" AS "Client", "main$candidate"."netto" AS "Netto", "main$candidate"."phone" AS "Phone" FROM "main$candidate" WHERE ("main$candidate"."level" != ? OR "main$candidate"."level" IS NULL) AND "main$candidate"."status" = ? GROUP BY "main$candidate"."name"] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:288) at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:288) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.execRetrieveQuery(JdbcDataStore.java:282) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.execRetrieveQueries(JdbcDataStore.java:195) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.retrieve(JdbcDataStore.java:155) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.retrieve(JdbcDataStore.java:150) at com.mendix.connectionbus.retrieve.InternalRequest.executeQueries(QueryRequestType.scala:75) at com.mendix.connectionbus.retrieve.InternalRequest.execute(QueryRequestType.scala:54) at com.mendix.connectionbus.retrieve.DataStoreCaller.$anonfun$processOqlGetRequest$1(DataStoreCaller.scala:49) at scala.Option.map(Option.scala:146) at com.mendix.connectionbus.retrieve.DataStoreCaller.processOqlGetRequest(DataStoreCaller.scala:43) at com.mendix.connectionbus.retrieve.DataStoreCaller.$anonfun$getResult$1(DataStoreCaller.scala:36) at scala.collection.TraversableLike.$anonfun$flatMap$1(TraversableLike.scala:240) at scala.collection.IndexedSeqOptimized.foreach(IndexedSeqOptimized.scala:32) at scala.collection.IndexedSeqOptimized.foreach$(IndexedSeqOptimized.scala:29) at scala.collection.mutable.WrappedArray.foreach(WrappedArray.scala:37) at scala.collection.TraversableLike.flatMap(TraversableLike.scala:240) at scala.collection.TraversableLike.flatMap$(TraversableLike.scala:237) at scala.collection.AbstractTraversable.flatMap(Traversable.scala:104) at com.mendix.connectionbus.retrieve.DataStoreCaller.getResult(DataStoreCaller.scala:36) at com.mendix.connectionbus.retrieve.DataStoreCaller.getResult(DataStoreCaller.scala:34) at com.mendix.connectionbus.retrieve.GetRequestHandler.doRequest(GetRequestHandler.java:75) at com.mendix.connectionbus.RequestAnalyzer.doRequest(RequestAnalyzer.java:50) at com.mendix.connectionbus.ConnectionBusImpl.doRequest(ConnectionBusImpl.java:447) at com.mendix.basis.action.user.RetrieveOQLDataTableAction.executeAction(RetrieveOQLDataTableAction.java:87) at com.mendix.basis.action.user.RetrieveOQLDataTableAction.executeAction(RetrieveOQLDataTableAction.java:18) at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57) at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55) at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155) at com.mendix.basis.component.InternalCoreBase.retrieveOQLDataTable(InternalCoreBase.java:1099) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getResultFromOQL(SeriesDataGenerator.java:421) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getSeriesValues(SeriesDataGenerator.java:340) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getSeriesDataList(SeriesDataGenerator.java:84) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getSeriesDataList(SeriesDataGenerator.java:75) at com.mendix.webui.reporting.clientactions.ReportAction.execute(ReportAction.java:62) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleRequest$2(ClientRequestHandler.scala:183) at com.mendix.webui.requesthandling.helpers.StateHandling.withState(StateHandling.scala:33) at com.mendix.webui.requesthandling.helpers.StateHandling.withState$(StateHandling.scala:22) at com.mendix.webui.requesthandling.ClientRequestHandler.withState(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleRequest$1(ClientRequestHandler.scala:181) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleRequest$1$adapted(ClientRequestHandler.scala:167) at com.mendix.webui.requesthandling.helpers.ContextHandling.$anonfun$inContext$4(ContextHandling.scala:41) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:12) at com.mendix.basis.actionmanagement.IMonitoredAction$$anon$1.execute(IMonitoredAction.scala:47) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.basis.actionmanagement.IMonitoredAction.monitor(IMonitoredAction.scala:49) at com.mendix.basis.actionmanagement.IMonitoredAction.monitor$(IMonitoredAction.scala:25) at com.mendix.webui.requesthandling.helpers.ContextHandling$ClientMonitoredAction.monitor(ContextHandling.scala:49) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:41) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:27) at com.mendix.webui.requesthandling.ClientRequestHandler.inContext(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:24) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:19) at com.mendix.webui.requesthandling.ClientRequestHandler.inContext(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.ClientRequestHandler.handleRequest(ClientRequestHandler.scala:167) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleAction$1(ClientRequestHandler.scala:138) at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest(ProfileHandling.scala:14) at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest$(ProfileHandling.scala:10) at com.mendix.webui.requesthandling.ClientRequestHandler.profileRequest(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.ClientRequestHandler.handleAction(ClientRequestHandler.scala:138) at com.mendix.webui.requesthandling.ClientRequestHandler.processRequest(ClientRequestHandler.scala:101) at com.mendix.externalinterface.connector.RequestHandler.doProcessRequest(RequestHandler.java:40) at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:72) at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:69) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.external.connector.MxRuntimeConnector.processRequest(MxRuntimeConnector.java:75) at com.mendix.basis.impl.MxRuntimeImpl.processRequest(MxRuntimeImpl.java:872) at com.mendix.m2ee.appcontainer.server.handler.RuntimeHandler.service(RuntimeHandler.java:42) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:867) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:542) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1588) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1345) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1557) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1247) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) at org.eclipse.jetty.server.Server.handle(Server.java:502) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765) at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683) at java.lang.Thread.run(Thread.java:748) Caused by: org.hsqldb.HsqlException: expression not in aggregate or GROUP BY columns: PUBLIC."main$candidate"."lastworkedfor" 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.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:288) at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:288) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.execRetrieveQuery(JdbcDataStore.java:282) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.execRetrieveQueries(JdbcDataStore.java:195) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.retrieve(JdbcDataStore.java:155) at com.mendix.connectionbus.connections.jdbc.JdbcDataStore.retrieve(JdbcDataStore.java:150) at com.mendix.connectionbus.retrieve.InternalRequest.executeQueries(QueryRequestType.scala:75) at com.mendix.connectionbus.retrieve.InternalRequest.execute(QueryRequestType.scala:54) at com.mendix.connectionbus.retrieve.DataStoreCaller.$anonfun$processOqlGetRequest$1(DataStoreCaller.scala:49) at scala.Option.map(Option.scala:146) at com.mendix.connectionbus.retrieve.DataStoreCaller.processOqlGetRequest(DataStoreCaller.scala:43) at com.mendix.connectionbus.retrieve.DataStoreCaller.$anonfun$getResult$1(DataStoreCaller.scala:36) at scala.collection.TraversableLike.$anonfun$flatMap$1(TraversableLike.scala:240) at scala.collection.IndexedSeqOptimized.foreach(IndexedSeqOptimized.scala:32) at scala.collection.IndexedSeqOptimized.foreach$(IndexedSeqOptimized.scala:29) at scala.collection.mutable.WrappedArray.foreach(WrappedArray.scala:37) at scala.collection.TraversableLike.flatMap(TraversableLike.scala:240) at scala.collection.TraversableLike.flatMap$(TraversableLike.scala:237) at scala.collection.AbstractTraversable.flatMap(Traversable.scala:104) at com.mendix.connectionbus.retrieve.DataStoreCaller.getResult(DataStoreCaller.scala:36) at com.mendix.connectionbus.retrieve.DataStoreCaller.getResult(DataStoreCaller.scala:34) at com.mendix.connectionbus.retrieve.GetRequestHandler.doRequest(GetRequestHandler.java:75) at com.mendix.connectionbus.RequestAnalyzer.doRequest(RequestAnalyzer.java:50) at com.mendix.connectionbus.ConnectionBusImpl.doRequest(ConnectionBusImpl.java:447) at com.mendix.basis.action.user.RetrieveOQLDataTableAction.executeAction(RetrieveOQLDataTableAction.java:87) at com.mendix.basis.action.user.RetrieveOQLDataTableAction.executeAction(RetrieveOQLDataTableAction.java:18) at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57) at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55) at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155) at com.mendix.basis.component.InternalCoreBase.retrieveOQLDataTable(InternalCoreBase.java:1099) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getResultFromOQL(SeriesDataGenerator.java:421) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getSeriesValues(SeriesDataGenerator.java:340) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getSeriesDataList(SeriesDataGenerator.java:84) at com.mendix.webui.reporting.generation.SeriesDataGenerator.getSeriesDataList(SeriesDataGenerator.java:75) at com.mendix.webui.reporting.clientactions.ReportAction.execute(ReportAction.java:62) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleRequest$2(ClientRequestHandler.scala:183) at com.mendix.webui.requesthandling.helpers.StateHandling.withState(StateHandling.scala:33) at com.mendix.webui.requesthandling.helpers.StateHandling.withState$(StateHandling.scala:22) at com.mendix.webui.requesthandling.ClientRequestHandler.withState(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleRequest$1(ClientRequestHandler.scala:181) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleRequest$1$adapted(ClientRequestHandler.scala:167) at com.mendix.webui.requesthandling.helpers.ContextHandling.$anonfun$inContext$4(ContextHandling.scala:41) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:12) at com.mendix.basis.actionmanagement.IMonitoredAction$$anon$1.execute(IMonitoredAction.scala:47) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.basis.actionmanagement.IMonitoredAction.monitor(IMonitoredAction.scala:49) at com.mendix.basis.actionmanagement.IMonitoredAction.monitor$(IMonitoredAction.scala:25) at com.mendix.webui.requesthandling.helpers.ContextHandling$ClientMonitoredAction.monitor(ContextHandling.scala:49) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:41) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:27) at com.mendix.webui.requesthandling.ClientRequestHandler.inContext(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:24) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:19) at com.mendix.webui.requesthandling.ClientRequestHandler.inContext(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.ClientRequestHandler.handleRequest(ClientRequestHandler.scala:167) at com.mendix.webui.requesthandling.ClientRequestHandler.$anonfun$handleAction$1(ClientRequestHandler.scala:138) at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest(ProfileHandling.scala:14) at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest$(ProfileHandling.scala:10) at com.mendix.webui.requesthandling.ClientRequestHandler.profileRequest(ClientRequestHandler.scala:29) at com.mendix.webui.requesthandling.ClientRequestHandler.handleAction(ClientRequestHandler.scala:138) at com.mendix.webui.requesthandling.ClientRequestHandler.processRequest(ClientRequestHandler.scala:101) at com.mendix.externalinterface.connector.RequestHandler.doProcessRequest(RequestHandler.java:40) at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:72) at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:69) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.external.connector.MxRuntimeConnector.processRequest(MxRuntimeConnector.java:75) at com.mendix.basis.impl.MxRuntimeImpl.processRequest(MxRuntimeImpl.java:872) at com.mendix.m2ee.appcontainer.server.handler.RuntimeHandler.service(RuntimeHandler.java:42) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:867) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:542) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1588) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1345) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1557) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1247) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) at org.eclipse.jetty.server.Server.handle(Server.java:502) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765) at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683) at java.lang.Thread.run(Thread.java:748)       
asked
2 answers
2

The following excerpt from the error message is very important

Caused by: org.hsqldb.HsqlException: expression not in aggregate or GROUP BY columns: PUBLIC."main$candidate"."lastworkedfor"

Basically, when you use a GROUP BY statement, in the select part of your query you are only allowed to have columns?attributes that you group by or aggregates (e.g. COUNT or SUM). See for more information https://medium.com/@riccardoodone/the-love-hate-relationship-between-select-and-group-by-in-sql-4957b2a70229

 

If you want to avoid duplicates you will need to add another criteria to the query, for example get only those candidates with the latest application date.  Pseudocode bellow adapted from https://stackoverflow.com/questions/10452940/sql-select-newest-records-that-have-distinct-name-column

SELECT Candidate.Name, ...
FROM Candidate, ....
WHERE Candidate.applied_on = (SELECT max(Candidate.applied_on) from Candidate Temp where Candidate.name =Temp.name)

Hope this helps,

Andrej

answered
0

Thanks Andrej for taking the time to reply. While you helped a lot to understand the group by clause and now i use it good, the second part was not what I needed to achieve. (I think my way telling what I need was a bit to evasive) I meant no duplicates as in the example below.

 

What I actually have is:

Name                            Certificate

John                                PSL

John                                VCA

 

And what i want to achive is:

Name                              Certificate

John                                 PSL, VCA

 

If you can help me with an ideea of how to get this would be greatly appreciated. I found out in SQL is a simple string_agg in the select but in OQL have no idea.

Thanks

 

LE: or I can use a Data Grid with export to Excel button – same effect:)) – It was maybe stupid of me to ask but maybe there is a way of doing this in OQL as well. 

answered