Hi all,
I created a dynamic list which works fine using the following sql.
SELECT ncd.Health_Board as ‘Health Board’, ncd.Clinic_Name AS ‘Clinic’, mor.MoR as ‘Speciality’, MIN(DATE_FORMAT(ncd.Date_of_WMHN_assessment,‘%d/%m/%Y’)) AS ‘First Recruit’, MAX(DATE_FORMAT(ncd.Date_of_WMHN_assessment,‘%d/%m/%Y’)) AS ‘Last Recruit’, COUNT(*) AS ‘Total Recruited’
FROM ncmh_contacts_database ncd
LEFT JOIN ncmh.combobox_method_of_recruitment mor
ON mor.MoR_ID=ncd.Method_of_Recruitment
WHERE ncd.Participation_Status = ‘Assessment Complete’
AND ncd.Date_of_WMHN_assessment IS NOT NULL
AND ncd.Date_of_WMHN_assessment <> ‘’
AND ncd.Clinic_Name <> ‘’
AND ncd.Health_Board <> ‘’
AND ncd.Health_Board != ‘N/A’
GROUP BY ncd.Health_Board, ncd.Clinic_Name, ncd.Method_of_Recruitment
However, whenever I try to make a report based on any of the columns…the following errors occurs:
Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru’ at line 1
MORE:
Error: An error occured during report execution.
Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru’ at line 1
To get help please contact an administrator.
Details
net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru’ at line 1 at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:321) at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.createReport(TableReportEngine.java:254) at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecuteNormal(TableReportEngine.java:187) at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecuteNormal(TableReportEngine.java:148) at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecute(TableReportEngine.java:90) at net.datenwerke.rs.core.service.reportmanager.engine.ReportEngine.execute(ReportEngine.java:127) at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:186) at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:119) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exeucteReport(ReportExportServlet.java:527) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.doExportReport(ReportExportServlet.java:402) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exportReport(ReportExportServlet.java:333) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exportReportViaSession(ReportExportServlet.java:326) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.doGet(ReportExportServlet.java:164) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f.CGLIB$doGet$0() at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f$$FastClassByGuice$$d195d7a6.invoke() at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:110) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f.doGet() at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f.CGLIB$service$13() at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f$$FastClassByGuice$$d195d7a6.invoke() at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:110) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f.service() at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263) at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178) at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62) at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89) at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru’ at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) at com.mysql.jdbc.Util.getInstance(Util.java:383) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116) at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:300) … 58 more
Any idea why reports don’t work based on a working dynamic list?
Thanks in advance!
