dhis2-users team mailing list archive

Thread
Date

Aggregation Query Builder problem

To: "dhis2-users@xxxxxxxxxxxxxxxxxxx" <dhis2-users@xxxxxxxxxxxxxxxxxxx>, dhis_bmi4@xxxxxxxxxxxxxxxx, Roshan Hewapathirana <roshanhewapathirana@xxxxxxxxx>
From: sumudu weerasinghe <sumuduw00@xxxxxxxxx>
Date: Sat, 29 Mar 2014 19:04:58 +0530

Dear all,
I am trying customization of tracker for mental health information system
in Sri Lanka.
I want to calculate " number of patient <65 years"

   - *Age* is a attribute - value type - Age(Years)
   - I made aggregate query builder: [CA:1301]<65
   - test condition shows Run failed
   - error generate in catalina out is as follows


*(DefaultUserAuditService.java
[http-apr-8084-exec-7])org.springframework.jdbc.BadSqlGrammarException:
StatementCallback; bad SQL grammar [SELECT '0' as dataelementid, '0' as
categoryoptioncomboid, '0' as attributeoptioncomboid, ou.organisationunitid
as sourceid, '0' as periodid,'aggregated_from_tracker' as comment, '201403'
as periodIsoDate,'dataelementname' as dataelementname, 'optioncomboid' as
categoryoptioncomboname, ou.name <http://ou.name> as organisationunitname,
count(distinct(pi.patientid)) as value FROM  programinstance as pi INNER
JOIN patient p on p.patientid=pi.patientid  INNER JOIN organisationunit ou
ON ou.organisationunitid=p.organisationunitid  WHERE  EXISTS (   SELECT *
FROM patientattributevalue _pav  WHERE _pav.patientid=pi.patientid  AND
_pav.patientattributeid=1301 AND _pav.value  < 65 ) GROUP BY
ou.organisationunitid, ou.name <http://ou.name>]; nested exception is
org.postgresql.util.PSQLException: ERROR: operator does not exist:
character varying < integer  Hint: No operator matches the given name and
argument type(s). You might need to add explicit type casts.*




   - This sql was able to correct by casting as follows

* .....AND _pav.patientattributeid=1301 AND Cast(_pav.value as integer)  <
65 ) GROUP BY ou.organisationunitid, ou.name......*

I am using

   - dhis 2.14
   - Build revision:13832
   - Database type: PostgreSQL
   - OS name:Windows 7
   - OS architecture:x86

Are there any error in my method of test ??
I will attach screen shots and log file.

Regards,
Sumudu

        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoin
t.java:2430)
        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.postgresql.util.PSQLException: ERROR: operator does not exist: ch
aracter varying < integer
  Hint: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
  Position: 661
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:560)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:403)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:283)
        at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyState
ment.java:35)
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.do
InStatement(JdbcTemplate.java:441)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
396)
        ... 113 more
* INFO  2014-03-29 11:37:54,146 Login success for user: 'admin', ip: '127.0.0.1'
 (DefaultUserAuditService.java [http-apr-8084-exec-5])
* INFO  2014-03-29 18:11:31,570 Login success for user: 'admin', ip: '127.0.0.1'
 (DefaultUserAuditService.java [http-apr-8084-exec-7])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [SELECT '0' as dataelementid, '0' as categoryoptioncomboid, '0' as attribute
optioncomboid, ou.organisationunitid as sourceid, '0' as periodid,'aggregated_fr
om_tracker' as comment, '201403' as periodIsoDate,'dataelementname' as dataeleme
ntname, 'optioncomboid' as categoryoptioncomboname, ou.name as organisationunitn
ame,  count(distinct(pi.patientid)) as value FROM  programinstance as pi INNER J
OIN patient p on p.patientid=pi.patientid  INNER JOIN organisationunit ou ON ou.
organisationunitid=p.organisationunitid  WHERE  EXISTS (   SELECT * FROM patient
attributevalue _pav  WHERE _pav.patientid=pi.patientid  AND _pav.patientattribut
eid=1301 AND _pav.value  < 65 ) GROUP BY ou.organisationunitid, ou.name]; nested
 exception is org.postgresql.util.PSQLException: ERROR: operator does not exist:
 character varying < integer
  Hint: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
  Position: 661
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTra
nslate(SQLStateSQLExceptionTranslator.java:97)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat
or.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat
or.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat
or.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
407)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:45
6)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:46
4)
        at org.hisp.dhis.caseaggregation.hibernate.HibernateCaseAggregationCondi
tionStore.executeSQL(HibernateCaseAggregationConditionStore.java:944)
        at org.hisp.dhis.caseaggregation.DefaultCaseAggregationConditionService.
executeSQL(DefaultCaseAggregationConditionService.java:465)
        at sun.reflect.GeneratedMethodAccessor700.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflecti
on(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJo
inpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.
proceedWithInvocation(TransactionInterceptor.java:96)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.
invokeWithinTransaction(TransactionAspectSupport.java:260)
        at org.springframework.transaction.interceptor.TransactionInterceptor.in
voke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invok
e(ExposeInvocationInterceptor.java:91)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami
cAopProxy.java:204)
        at $Proxy80.executeSQL(Unknown Source)
        at org.hisp.dhis.patient.action.caseaggregation.TestCaseAggregationCondi
tionAction.execute(TestCaseAggregationConditionAction.java:111)
        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 com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultA
ctionInvocation.java:450)
        at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(Defa
ultActionInvocation.java:289)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:252)
        at org.hisp.dhis.webportal.interceptor.XWorkPortalUserInterceptor.interc
ept(XWorkPortalUserInterceptor.java:88)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.webportal.interceptor.XWorkPortalModuleInterceptor.inte
rcept(XWorkPortalModuleInterceptor.java:93)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.webportal.interceptor.XWorkPortalMenuInterceptor.interc
ept(XWorkPortalMenuInterceptor.java:97)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.webportal.interceptor.XWorkPortalParamsInterceptor.inte
rcept(XWorkPortalParamsInterceptor.java:147)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.interceptor.ContextInterceptor.intercept(ContextInterce
ptor.java:83)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.interceptor.UserSettingInterceptor.intercept(UserSettin
gInterceptor.java:81)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.security.intercept.LoginInterceptor.intercept(LoginInte
rceptor.java:85)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.security.intercept.XWorkSecurityInterceptor.intercept(X
WorkSecurityInterceptor.java:113)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.interceptor.SystemSettingInterceptor.intercept(SystemSe
ttingInterceptor.java:107)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.interceptor.I18nInterceptor.intercept(I18nInterceptor.j
ava:136)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept
(ParametersInterceptor.java:249)
        at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept
(MethodFilterInterceptor.java:98)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.inter
cept(StaticParametersInterceptor.java:191)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.hisp.dhis.interceptor.ExceptionInterceptor.intercept(ExceptionInt
erceptor.java:110)
        at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionI
nvocation.java:246)
        at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.j
ava:54)
        at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.jav
a:562)
        at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(Exec
uteOperations.java:77)
        at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter
.doFilter(StrutsPrepareAndExecuteFilter.java:99)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:243)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:210)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:330)
        at org.springframework.security.web.access.intercept.FilterSecurityInter
ceptor.invoke(FilterSecurityInterceptor.java:118)
        at org.springframework.security.web.access.intercept.FilterSecurityInter
ceptor.doFilter(FilterSecurityInterceptor.java:84)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.access.ExceptionTranslationFilter.do
Filter(ExceptionTranslationFilter.java:113)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.session.SessionManagementFilter.doFi
lter(SessionManagementFilter.java:103)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.AnonymousAuthenticati
onFilter.doFilter(AnonymousAuthenticationFilter.java:113)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwar
eRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter
.doFilter(RequestCacheAwareFilter.java:45)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.www.BasicAuthenticati
onFilter.doFilter(BasicAuthenticationFilter.java:150)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.AbstractAuthenticatio
nProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.hisp.dhis.security.filter.CustomAuthenticationFilter.doFilter(Cus
tomAuthenticationFilter.java:64)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.logout.LogoutFilter.d
oFilter(LogoutFilter.java:105)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.hisp.dhis.security.filter.AutomaticAccessFilter.doFilter(Automati
cAccessFilter.java:113)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.context.SecurityContextPersistenceFi
lter.doFilter(SecurityContextPersistenceFilter.java:87)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.
doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(Fi
lterChainProxy.java:192)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChai
nProxy.java:160)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(D
elegatingFilterProxy.java:343)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(Delegat
ingFilterProxy.java:260)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:243)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:210)
        at org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.do
FilterInternal(OpenSessionInViewFilter.java:149)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
equestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:243)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:210)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterIntern
al(CharacterEncodingFilter.java:88)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
equestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:243)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:210)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:222)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:123)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
torBase.java:502)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:171)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:100)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:
953)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
a:409)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp
11Processor.java:1044)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(
AbstractProtocol.java:607)
        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpo
int.java:2441)
        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoin
t.java:2430)
        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.postgresql.util.PSQLException: ERROR: operator does not exist: ch
aracter varying < integer
  Hint: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
  Position: 661
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:560)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:403)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:283)
        at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyState
ment.java:35)
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.do
InStatement(JdbcTemplate.java:441)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
396)
        ... 114 more

Attachment: aggregate query_29_3_2014.JPG
Description: JPEG image

Follow ups

Re: Aggregation Query Builder problem
From: Manaf Abthul, 2014-03-29