Can not run my query in OpenMRS 2x

hpardess · May 13, 2017, 9:11pm

I wrote following query to return the count of patient for each diagnosis,

(SELECT 
(SELECT `name` FROM `concept_name` 
WHERE `voided` = 0 AND `locale` IN ('en','pa') AND `concept_id` = diagnose.`value_coded`  
AND `concept_name_type` = 'FULLY_SPECIFIED' ORDER BY `locale` DESC LIMIT 1) AS DiagnoseName, 'Coded' AS 'Type', COUNT(*) AS 'Total Patients'

FROM encounter AS visit_note_encounter, obs AS diagnose, obs AS diagnose_type, person, person_name, patient_identifier
WHERE visit_note_encounter.voided = 0 
AND visit_note_encounter.encounter_type = 5
AND DATE(visit_note_encounter.encounter_datetime) BETWEEN :startDate AND :endDate
AND diagnose.voided = 0	
AND diagnose.encounter_id = visit_note_encounter.encounter_id
AND diagnose.concept_id = 1284 -- it is coded diagnose concept - PROBLEM LIST
AND diagnose_type.encounter_id = visit_note_encounter.encounter_id
AND diagnose.obs_group_id = diagnose_type.obs_group_id AND diagnose_type.concept_id = 159946 
AND diagnose_type.value_coded = 159943

AND person.person_id = person_name.person_id 
AND person.person_id = patient_identifier.patient_id
AND person.voided=0 
AND person_name.voided=0 
AND patient_identifier.voided=0
AND person_name.preferred=1 
AND patient_identifier.preferred=1 
AND patient_identifier.identifier_type=3
AND patient_identifier.patient_id = visit_note_encounter.patient_id
GROUP BY DiagnoseName
ORDER BY DiagnoseName ASC)

UNION ALL

(SELECT diagnose.value_text AS DiagnoseName, 'Non-Coded' AS 'Type', COUNT(*) AS 'Total Patients'

FROM encounter AS visit_note_encounter, obs AS diagnose, obs AS diagnose_type, person, person_name, patient_identifier
WHERE visit_note_encounter.voided = 0 
AND visit_note_encounter.encounter_type = 5
AND DATE(visit_note_encounter.encounter_datetime) BETWEEN :startDate AND :endDate
AND diagnose.voided = 0
AND diagnose.encounter_id = visit_note_encounter.encounter_id
AND diagnose.concept_id = 161602 -- it is coded diagnose concept - Diagnosis or problem, non-coded
AND diagnose_type.encounter_id = visit_note_encounter.encounter_id
AND diagnose.obs_group_id = diagnose_type.obs_group_id AND diagnose_type.concept_id = 159946 
AND diagnose_type.value_coded = 159943

AND person.person_id = person_name.person_id 
AND person.person_id = patient_identifier.patient_id
AND person.voided=0 
AND person_name.voided=0 
AND patient_identifier.voided=0
AND person_name.preferred=1 
AND patient_identifier.preferred=1 
AND patient_identifier.identifier_type=3
AND patient_identifier.patient_id = visit_note_encounter.patient_id
GROUP BY DiagnoseName
ORDER BY DiagnoseName ASC)

If I run above query in SQL editor i get the result

but if i run it in Data Set Definitions (SQL Query) I get following error message,

type Exception report

message Request processing failed; nested exception is org.openmrs.module.reporting.evaluation.EvaluationException: Failed to evaluate Error evaluating because:

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.openmrs.module.reporting.evaluation.EvaluationException: Failed to evaluate Error evaluating because:
Unable to retrieve columns for query
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:72)
	org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:57)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.xforms.web.XformsFilter.doFilter(XformsFilter.java:69)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:54)
	org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:108)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

org.openmrs.module.reporting.evaluation.EvaluationException: Failed to evaluate Error evaluating because:
Unable to retrieve columns for query
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.executeEvaluator(BaseDefinitionService.java:375)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.evaluateBypassingExclusionOfTestPatients(BaseDefinitionService.java:266)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.evaluate(BaseDefinitionService.java:231)
	org.openmrs.module.reporting.dataset.definition.service.DataSetDefinitionServiceImpl.evaluate(DataSetDefinitionServiceImpl.java:45)
	org.openmrs.module.reporting.dataset.definition.service.DataSetDefinitionServiceImpl.evaluate(DataSetDefinitionServiceImpl.java:29)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:121)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	com.sun.proxy.$Proxy117.evaluate(Unknown Source)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201)
	com.sun.proxy.$Proxy237.evaluate(Unknown Source)
	org.openmrs.module.reporting.definition.DefinitionContext.evaluate(DefinitionContext.java:124)
	org.openmrs.module.reporting.evaluation.parameter.ParameterizableUtil.evaluateParameterizable(ParameterizableUtil.java:56)
	org.openmrs.module.reporting.web.controller.QueryParameterFormController.processForm(QueryParameterFormController.java:111)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:177)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:446)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:434)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:72)
	org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:57)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.xforms.web.XformsFilter.doFilter(XformsFilter.java:69)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:54)
	org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:108)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

java.lang.IllegalArgumentException: Unable to retrieve columns for query
	org.openmrs.module.reporting.evaluation.querybuilder.SqlQueryBuilder.getColumns(SqlQueryBuilder.java:129)
	org.openmrs.module.reporting.evaluation.service.EvaluationServiceImpl.getColumns(EvaluationServiceImpl.java:40)
	sun.reflect.GeneratedMethodAccessor1256.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:121)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	com.sun.proxy.$Proxy105.getColumns(Unknown Source)
	sun.reflect.GeneratedMethodAccessor1256.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	com.sun.proxy.$Proxy271.getColumns(Unknown Source)
	org.openmrs.module.reporting.dataset.definition.evaluator.SqlDataSetEvaluator.evaluate(SqlDataSetEvaluator.java:85)
	org.openmrs.module.reporting.dataset.definition.evaluator.SqlDataSetEvaluator.evaluate(SqlDataSetEvaluator.java:41)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.executeEvaluator(BaseDefinitionService.java:367)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.evaluateBypassingExclusionOfTestPatients(BaseDefinitionService.java:266)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.evaluate(BaseDefinitionService.java:231)
	org.openmrs.module.reporting.dataset.definition.service.DataSetDefinitionServiceImpl.evaluate(DataSetDefinitionServiceImpl.java:45)
	org.openmrs.module.reporting.dataset.definition.service.DataSetDefinitionServiceImpl.evaluate(DataSetDefinitionServiceImpl.java:29)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:121)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	com.sun.proxy.$Proxy117.evaluate(Unknown Source)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201)
	com.sun.proxy.$Proxy237.evaluate(Unknown Source)
	org.openmrs.module.reporting.definition.DefinitionContext.evaluate(DefinitionContext.java:124)
	org.openmrs.module.reporting.evaluation.parameter.ParameterizableUtil.evaluateParameterizable(ParameterizableUtil.java:56)
	org.openmrs.module.reporting.web.controller.QueryParameterFormController.processForm(QueryParameterFormController.java:111)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:177)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:446)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:434)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:72)
	org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:57)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.xforms.web.XformsFilter.doFilter(XformsFilter.java:69)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:54)
	org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:108)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

java.lang.NullPointerException
	org.openmrs.module.reporting.evaluation.querybuilder.SqlQueryBuilder$1.execute(SqlQueryBuilder.java:112)
	org.hibernate.jdbc.WorkExecutor.executeWork(WorkExecutor.java:54)
	org.hibernate.internal.SessionImpl$2.accept(SessionImpl.java:2076)
	org.hibernate.internal.SessionImpl$2.accept(SessionImpl.java:2073)
	org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:313)
	org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2095)
	org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2080)
	org.openmrs.api.db.hibernate.DbSession.doWork(DbSession.java:1005)
	org.openmrs.module.reporting.evaluation.querybuilder.SqlQueryBuilder.getColumns(SqlQueryBuilder.java:104)
	org.openmrs.module.reporting.evaluation.service.EvaluationServiceImpl.getColumns(EvaluationServiceImpl.java:40)
	sun.reflect.GeneratedMethodAccessor1256.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:121)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	com.sun.proxy.$Proxy105.getColumns(Unknown Source)
	sun.reflect.GeneratedMethodAccessor1256.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	com.sun.proxy.$Proxy271.getColumns(Unknown Source)
	org.openmrs.module.reporting.dataset.definition.evaluator.SqlDataSetEvaluator.evaluate(SqlDataSetEvaluator.java:85)
	org.openmrs.module.reporting.dataset.definition.evaluator.SqlDataSetEvaluator.evaluate(SqlDataSetEvaluator.java:41)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.executeEvaluator(BaseDefinitionService.java:367)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.evaluateBypassingExclusionOfTestPatients(BaseDefinitionService.java:266)
	org.openmrs.module.reporting.definition.service.BaseDefinitionService.evaluate(BaseDefinitionService.java:231)
	org.openmrs.module.reporting.dataset.definition.service.DataSetDefinitionServiceImpl.evaluate(DataSetDefinitionServiceImpl.java:45)
	org.openmrs.module.reporting.dataset.definition.service.DataSetDefinitionServiceImpl.evaluate(DataSetDefinitionServiceImpl.java:29)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:121)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	com.sun.proxy.$Proxy117.evaluate(Unknown Source)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201)
	com.sun.proxy.$Proxy237.evaluate(Unknown Source)
	org.openmrs.module.reporting.definition.DefinitionContext.evaluate(DefinitionContext.java:124)
	org.openmrs.module.reporting.evaluation.parameter.ParameterizableUtil.evaluateParameterizable(ParameterizableUtil.java:56)
	org.openmrs.module.reporting.web.controller.QueryParameterFormController.processForm(QueryParameterFormController.java:111)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:177)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:446)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:434)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:72)
	org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:57)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.xforms.web.XformsFilter.doFilter(XformsFilter.java:69)
	org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70)
	org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:54)
	org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:108)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

https://modules-refapp.openmrs.org/openmrs/module/reporting/datasets/sqlDataSetEditor.form?uuid=066d384a-d38d-4e77-bac3-3552486ad73f

dkayiwa · May 14, 2017, 8:12pm

I have modified your sql statement to begin with a select by prefixing it with “SELECT * FROM (” and ending it with “) t” So can you try to run it again using the above link that you gave?

hpardess · May 14, 2017, 8:28pm

Thank you. Now the query is executed . May I know the reason of this change in query?

you have just changed it to a single query with sub-query which will be executed slower in larger data-set.

dkayiwa · May 14, 2017, 8:59pm

Here we go: https://bugs.mysql.com/bug.php?id=16734

hpardess · May 15, 2017, 11:49am

Thank you for sharing.