Tracker Problem

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

image

···
  • **Aggregation Query Builder Management screen shot attach here **

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

···

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
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, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My
SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space

  • I tested it on

  • Crome as browser ,

  • Database MySQL Server 5.1

  • Java jdk1.7.0_04

  • server apache-tomcat-7.0.50

  • Aggregation Query Builder Management screen shot and full log file attach here

aggregate query_2_3_2014.txt (22.8 KB)

···

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

···

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

···

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

···

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Sumudu,

Which revision in 2.14 version do you used for testing ? Can you help me to test with R. 13799 or above ?

···

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Thank you Tran,

Yes it was old Revision Now ,

I downloaded R 13806 for 2.14 and

R12980 for 2.13 try on both.

  • Aggregate query builder test button work and no error massage
  • Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu.
  • But that value **not shows in reports/ data entry form or mysql database datavalue table ** ?
···

On Thu, Mar 6, 2014 at 9:44 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sumudu,

Which revision in 2.14 version do you used for testing ? Can you help me to test with R. 13799 or above ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi

Dear All

I try with R12980 in 2.13 to aggregate patient base data.

My agree gate data element is All female patient. My aggregated query is [CP:gender] =‘F’ is not null . But

1 I didn’t get all the number of patient in manual person aggregation i enter (including male also ,in screen print) is my quarry wrong?

2 I didn’t get any aggregated reports in data set report( IN screen print ) Why is this ? I have run the data mart and up dated the resource tables

I have attached my Catalina.out file

What is the wrong ?

Best Regards

Gayan Yasantha

catalina.out (79.6 KB)

···

On Fri, Mar 7, 2014 at 11:54 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thank you Tran,

Yes it was old Revision Now ,

I downloaded R 13806 for 2.14 and

R12980 for 2.13 try on both.

  • Aggregate query builder test button work and no error massage
  • Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu.
  • But that value **not shows in reports/ data entry form or mysql database datavalue table ** ?

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Gayan Yasantha Piyasena

On Thu, Mar 6, 2014 at 9:44 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sumudu,

Which revision in 2.14 version do you used for testing ? Can you help me to test with R. 13799 or above ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi tran

I am using war file R12980. I have test Aggregate query builder test button and it success quarry and no run fail massage then Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu. then i have save it into data base But that value not shows in reports/ data entry form or mysql database data-value table.

I have found the error massage it says Data Integrity Violation Exception in Catalina.out file .

i.e.

... 121 more
  • INFO 2014-03-10 13:10:54,939 ‘admin’ update org.hisp.dhis.caseaggregation.CaseAggregationCondition, name: Multi- bacillary, uid: D7TMQF7l6o1 (AuditLogUtil.java [http-bio-8080-exec-8])

  • ERROR 2014-03-10 13:12:22,494 Error while executing action (ExceptionInterceptor.java [http-bio-8080-exec-3])
    org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO datavalue (dataelementid, categoryoptioncomboid, sourceid, periodid, comment, value)SELECT ‘21’ as dataelementid, ‘1’ as categoryoptioncomboid, ou.organisationunitid as sourceid, ‘1’ as periodid,‘DHIS-SYSTEM’ as comment, COUNT(DISTINCT(pi.patientid)) as value FROM programinstance as pi INNER JOIN patient p on p.patientid=pi.patientid INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstanceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationunitid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstageinstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=1 AND _psi.organisationunitid in (12) AND _pi.programid = 1 AND _psi.executionDate>=‘2014-03-01’ AND _psi.executionDate <= ‘2014-03-31’ AND _psi.programstageid = 1 AND _pdv.value =‘MB-A’ + ‘MB-C’ ) GROUP BY ou.organisationunitid, ou.name]; Data truncation: Truncated incorrect DOUBLE value: ‘MB-C’; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ‘MB-C’

Thank you

Gayan

catalina.out (197 KB)

···

On Sat, Mar 8, 2014 at 8:54 AM, Gayan Yasantha gayanyp@gmail.com wrote:

Hi

Dear All

I try with R12980 in 2.13 to aggregate patient base data.

My agree gate data element is All female patient. My aggregated query is [CP:gender] =‘F’ is not null . But

1 I didn’t get all the number of patient in manual person aggregation i enter (including male also ,in screen print) is my quarry wrong?

2 I didn’t get any aggregated reports in data set report( IN screen print ) Why is this ? I have run the data mart and up dated the resource tables

I have attached my Catalina.out file

What is the wrong ?

Best Regards

Gayan Yasantha


Gayan Yasantha Piyasena

On Fri, Mar 7, 2014 at 11:54 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thank you Tran,

Yes it was old Revision Now ,

I downloaded R 13806 for 2.14 and

R12980 for 2.13 try on both.

  • Aggregate query builder test button work and no error massage
  • Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu.
  • But that value **not shows in reports/ data entry form or mysql database datavalue table ** ?

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Gayan Yasantha Piyasena

On Thu, Mar 6, 2014 at 9:44 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sumudu,

Which revision in 2.14 version do you used for testing ? Can you help me to test with R. 13799 or above ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Gayan,

this issue should have been fixed today in 2.14. Download again from dhis2.org.

regards,

Lars

···

On Sat, Mar 8, 2014 at 8:54 AM, Gayan Yasantha gayanyp@gmail.com wrote:

Hi

Dear All

I try with R12980 in 2.13 to aggregate patient base data.

My agree gate data element is All female patient. My aggregated query is [CP:gender] =‘F’ is not null . But

1 I didn’t get all the number of patient in manual person aggregation i enter (including male also ,in screen print) is my quarry wrong?

2 I didn’t get any aggregated reports in data set report( IN screen print ) Why is this ? I have run the data mart and up dated the resource tables

I have attached my Catalina.out file

What is the wrong ?

Best Regards

Gayan Yasantha


Gayan Yasantha Piyasena

On Fri, Mar 7, 2014 at 11:54 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thank you Tran,

Yes it was old Revision Now ,

I downloaded R 13806 for 2.14 and

R12980 for 2.13 try on both.

  • Aggregate query builder test button work and no error massage
  • Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu.
  • But that value **not shows in reports/ data entry form or mysql database datavalue table ** ?

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Gayan Yasantha Piyasena

On Thu, Mar 6, 2014 at 9:44 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sumudu,

Which revision in 2.14 version do you used for testing ? Can you help me to test with R. 13799 or above ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Lars,

What is the revision ? is it R13807 ?

I have down load it from this http://www.dhis2.org/downloads.

···

On Tue, Mar 11, 2014 at 3:27 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Gayan,

this issue should have been fixed today in 2.14. Download again from dhis2.org.

regards,

Lars

On Mar 10, 2014 9:51 AM, “Gayan Yasantha” gayanyp@gmail.com wrote:

Hi tran

I am using war file R12980. I have test Aggregate query builder test button and it success quarry and no run fail massage then Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu. then i have save it into data base But that value not shows in reports/ data entry form or mysql database data-value table.

I have found the error massage it says Data Integrity Violation Exception in Catalina.out file .

i.e.

... 121 more
  • INFO 2014-03-10 13:10:54,939 ‘admin’ update org.hisp.dhis.caseaggregation.CaseAggregationCondition, name: Multi- bacillary, uid: D7TMQF7l6o1 (AuditLogUtil.java [http-bio-8080-exec-8])

  • ERROR 2014-03-10 13:12:22,494 Error while executing action (ExceptionInterceptor.java [http-bio-8080-exec-3])
    org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO datavalue (dataelementid, categoryoptioncomboid, sourceid, periodid, comment, value)SELECT ‘21’ as dataelementid, ‘1’ as categoryoptioncomboid, ou.organisationunitid as sourceid, ‘1’ as periodid,‘DHIS-SYSTEM’ as comment, COUNT(DISTINCT(pi.patientid)) as value FROM programinstance as pi INNER JOIN patient p on p.patientid=pi.patientid INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstanceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationunitid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstageinstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=1 AND _psi.organisationunitid in (12) AND _pi.programid = 1 AND _psi.executionDate>=‘2014-03-01’ AND _psi.executionDate <= ‘2014-03-31’ AND _psi.programstageid = 1 AND _pdv.value =‘MB-A’ + ‘MB-C’ ) GROUP BY ou.organisationunitid, ou.name]; Data truncation: Truncated incorrect DOUBLE value: ‘MB-C’; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ‘MB-C’

Thank you

Gayan


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On Sat, Mar 8, 2014 at 8:54 AM, Gayan Yasantha gayanyp@gmail.com wrote:

Hi

Dear All

I try with R12980 in 2.13 to aggregate patient base data.

My agree gate data element is All female patient. My aggregated query is [CP:gender] =‘F’ is not null . But

1 I didn’t get all the number of patient in manual person aggregation i enter (including male also ,in screen print) is my quarry wrong?

2 I didn’t get any aggregated reports in data set report( IN screen print ) Why is this ? I have run the data mart and up dated the resource tables

I have attached my Catalina.out file

What is the wrong ?

Best Regards

Gayan Yasantha


Gayan Yasantha Piyasena

On Fri, Mar 7, 2014 at 11:54 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thank you Tran,

Yes it was old Revision Now ,

I downloaded R 13806 for 2.14 and

R12980 for 2.13 try on both.

  • Aggregate query builder test button work and no error massage
  • Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu.
  • But that value **not shows in reports/ data entry form or mysql database datavalue table ** ?

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Gayan Yasantha Piyasena

On Thu, Mar 6, 2014 at 9:44 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sumudu,

Which revision in 2.14 version do you used for testing ? Can you help me to test with R. 13799 or above ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Lars,
I have download the war file of 2.14 and used it . For one orgunit and one aggregated quarry it work well data set report came . But
when I register a person to other orgunit and for same quarry data set report didnt appear . I have found this error in Catalina.out file

ERROR 2014-03-11 12:38:34,527 Error while executing action (ExceptionInterceptor.java [http-bio-8080-exec-12])
org.springframework.dao.DuplicateKeyException: StatementCallback; SQL [INSERT INTO datavalue (dataelementid, categoryoptioncomboid, attributeoptioncomboid, sourceid, periodid, comment, value) SELECT ‘19’ as dataelementid, ‘1’ as categoryoptioncomboid, ‘1’ as attributeoptioncomboid, ou.organisationunitid as sourceid, ‘3’ as periodid,‘DHIS-SYSTEM’ as comment, 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=2 AND _pav.value =‘Female’ ) GROUP BY ou.organisationunitid, ou.name]; Duplicate entry ‘19-3-12-1-1’ for key ‘PRIMARY’; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘19-3-12-1-1’ for key ‘PRIMARY’

Regards

Gayan

catalina.out (128 KB)

···

On Tue, Mar 11, 2014 at 9:32 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Lars,

What is the revision ? is it R13807 ?

I have down load it from this http://www.dhis2.org/downloads.


Gayan Yasantha Piyasena

On Tue, Mar 11, 2014 at 3:27 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Gayan,

this issue should have been fixed today in 2.14. Download again from dhis2.org.

regards,

Lars

On Mar 10, 2014 9:51 AM, “Gayan Yasantha” gayanyp@gmail.com wrote:

Hi tran

I am using war file R12980. I have test Aggregate query builder test button and it success quarry and no run fail massage then Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu. then i have save it into data base But that value not shows in reports/ data entry form or mysql database data-value table.

I have found the error massage it says Data Integrity Violation Exception in Catalina.out file .

i.e.

... 121 more
  • INFO 2014-03-10 13:10:54,939 ‘admin’ update org.hisp.dhis.caseaggregation.CaseAggregationCondition, name: Multi- bacillary, uid: D7TMQF7l6o1 (AuditLogUtil.java [http-bio-8080-exec-8])

  • ERROR 2014-03-10 13:12:22,494 Error while executing action (ExceptionInterceptor.java [http-bio-8080-exec-3])
    org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO datavalue (dataelementid, categoryoptioncomboid, sourceid, periodid, comment, value)SELECT ‘21’ as dataelementid, ‘1’ as categoryoptioncomboid, ou.organisationunitid as sourceid, ‘1’ as periodid,‘DHIS-SYSTEM’ as comment, COUNT(DISTINCT(pi.patientid)) as value FROM programinstance as pi INNER JOIN patient p on p.patientid=pi.patientid INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstanceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationunitid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstageinstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=1 AND _psi.organisationunitid in (12) AND _pi.programid = 1 AND _psi.executionDate>=‘2014-03-01’ AND _psi.executionDate <= ‘2014-03-31’ AND _psi.programstageid = 1 AND _pdv.value =‘MB-A’ + ‘MB-C’ ) GROUP BY ou.organisationunitid, ou.name]; Data truncation: Truncated incorrect DOUBLE value: ‘MB-C’; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ‘MB-C’

Thank you

Gayan


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On Sat, Mar 8, 2014 at 8:54 AM, Gayan Yasantha gayanyp@gmail.com wrote:

Hi

Dear All

I try with R12980 in 2.13 to aggregate patient base data.

My agree gate data element is All female patient. My aggregated query is [CP:gender] =‘F’ is not null . But

1 I didn’t get all the number of patient in manual person aggregation i enter (including male also ,in screen print) is my quarry wrong?

2 I didn’t get any aggregated reports in data set report( IN screen print ) Why is this ? I have run the data mart and up dated the resource tables

I have attached my Catalina.out file

What is the wrong ?

Best Regards

Gayan Yasantha


Gayan Yasantha Piyasena

On Fri, Mar 7, 2014 at 11:54 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thank you Tran,

Yes it was old Revision Now ,

I downloaded R 13806 for 2.14 and

R12980 for 2.13 try on both.

  • Aggregate query builder test button work and no error massage
  • Manual person aggregation also work well and show that data is pass to aggregate data elements in preview menu.
  • But that value **not shows in reports/ data entry form or mysql database datavalue table ** ?

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Gayan Yasantha Piyasena

On Thu, Mar 6, 2014 at 9:44 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sumudu,

Which revision in 2.14 version do you used for testing ? Can you help me to test with R. 13799 or above ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Now it is working in 2.13. But 2.14 still having same problem and sending same error message in Mysql sql.?

On Mon, Mar 3, 2014 at 12:23 AM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Hi Tran,
Yes, your correct it was R12979.I have update with R12980. It is working now!
Many thanks for your help.

Sumudu

On Sun, Mar 2, 2014 at 7:59 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

Hi Sunudu,

Which is the revision you used ? It should be R. 12980. Can you check this revision on you side ?

Best regards,


Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

Thanks Tran.

I try it again in following steps but not worked

  1. export xml metadata

  2. I download 2.13 from the site on 28 feb from (http://www.dhis2.org/downloads)

  3. replace it with my previous war file and deployed

  4. metadata imported

  5. some missing part of person data created again according to user guideline

  6. try to create query builder in it ,but it is still not working

  7. Similar error reported as

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gram
mar [

  • ERROR 2014-03-02 10:48:54,455 Error while executing action (ExceptionIntercept

or.java [http-apr-8084-exec-7])
SELECT ‘0’ as dataelementid, ‘0’ as categoryoptioncomboid, ou.organisationu

nitid as sourceid, ‘0’ as periodid,‘DHIS-SYSTEM’ as comment, ‘201403’ 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.patienti

d INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstan

ceid INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationun

itid WHERE EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstag

einstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN p

rograminstance _pi ON _pi.programinstanceid=_psi.programinstanceid WHERE psi.pro

gramstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=40 AND _

psi.organisationunitid in (0) AND _pi.programid = 1 AND _psi.executionDate>=’

2014-03-02’ AND _psi.executionDate <= ‘2014-03-02’ AND _psi.programstageid = 1

AND _pdv.value is not null ) GROUP BY ou.organisationunitid, ou.name] jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Yo
u have an error in your SQL syntax; check the manual that corresponds to your My

SQL server version for the right syntax to use near 'distinct(pi.patientid) ) as

value FROM programinstance as pi INNER JOIN patien’ at line 1

  • Two errors i found are marked in red 1st extra space 2nd no space
  • I tested it on
  • Crome as browser ,
  • Database MySQL Server 5.1
  • Java jdk1.7.0_04
  • server apache-tomcat-7.0.50
  • Aggregation Query Builder Management screen shot and full log file attach here

On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau tran.hispvietnam@gmail.com wrote:

HI Sumudu,

I fixed it for 2.13. Please test it on your site again. Thank you.

Best regards,

Châu Thu Trân
HISP Viet Nam
Email: tran.hispvietnam@gmail.com

On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 **

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi,

I am unable to access the fred-api at http://[myserver:8080]/api-fred/v1/facilities.json on this release. All other functionality seems present.
Is there an updated URL available or is the fred api not included with this version?

Thanks

Pierre

Hi,

In the 2.15 release, we have moved the endpoint for fred to /ohie/fred (from /api-fred).

···


Morten

On Tue, Mar 11, 2014 at 4:57 PM, Pierre Dane pierre@jembi.org wrote:

Hi,

I am unable to access the fred-api at http://[myserver:8080]/api-fred/v1/facilities.json on this release. All other functionality seems present.

Is there an updated URL available or is the fred api not included with this version?

Thanks

Pierre


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Pierre,

You can take a look at the FRED API on the demo server here: https://apps.dhis2.org/demo/ohie/fred/v1/facilities.

The docs seem to be out of date. I’ve made the fix, but I’m not part of the documenters team on lauchpad, so I’ve attached the patch here.

Cheers,

Pascal

dhis2_user_man_web_api.xml.patch (2.92 KB)

···

On 11 March 2014 12:07, Morten Olav Hansen mortenoh@gmail.com wrote:

Hi,

In the 2.15 release, we have moved the endpoint for fred to /ohie/fred (from /api-fred).


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


*Pascal Brandt
Senio**r Software Developer, Jembi Health Systems | SOUTH AFRICA

Mobile: +27 84 827 9342 | Office: +27 21 701 0939 | Skype: psbrandt

E-mail: pascal@jembi.org*


Morten

On Tue, Mar 11, 2014 at 4:57 PM, Pierre Dane pierre@jembi.org wrote:

Hi,

I am unable to access the fred-api at http://[myserver:8080]/api-fred/v1/facilities.json on this release. All other functionality seems present.

Is there an updated URL available or is the fred api not included with this version?

Thanks

Pierre


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp