dhis2-users team mailing list archive

[sumudu weerasinghe <sumuduw00@xxxxxxxxx>]

*Dear All,*
*I am Sumudu from Sri lanka, my research interest in health information
management system,  new to dhis and try to customize dhis to Mental health
unit . i had customized aggregate part with out a problem. But when i try
to aggregate and pass patient data by tracker to my aggregate part, it was
not work properly *

   - *My dhis version is 2.13*
   - *Database Mysql*

*When i try on sample as mention in user_manual (26.2.1.8.2. Sample of
person aggregation)*

   - *Aggregation Query Builder Management test condition shows as "Run
   fail"*


>    - *Error show as follows*
>
> org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad
SQL gram
mar [SELECT '0' as dataelementid, '0' as categoryoptioncomboid,
ou.organisationu
nitid as sourceid, '0' as periodid,'DHIS-SYSTEM' as comment, '201402' as
periodI
soDate,'dataelementname' as dataelementname, 'optioncomboid' as
categoryoptionco
mboname, ou.name as organisationunitname,  count (distinct(pi.patientid) )
as va
lue 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 _p.patientid FROM patient _p WHERE _p.patientid =
pi.patie
ntid AND gender ='M' ) GROUP BY ou.organisationunitid, ou.name]; nested
exceptio
n is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
erro
r in your SQL syntax; check the manual that corresponds to your MySQL
server ver
sion for the right syntax to use near 'distinct(pi.patientid) ) as value
FROM  p
rograminstance as pi INNER JOIN patient' at line 1

>
>    - * Aggregation Query Builder Management screen shot attach here *
>
>

Attachment: aggregate query.JPG
Description: JPEG image