Aggregation Query Builder problem

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 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]; 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

aggregate query_29_3_2014.txt (16.6 KB)

image

Hi sumudu,

It is working, testing is successful on Version:2.14 Build revision:13826.

I have checked for number of Asthma patient less than 12yrs. The age attribute is patient attribute.

Os: Win7

MySQL database

image

···

On 29 March 2014 19:04, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 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]; 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

You received this message because you are subscribed to the Google Groups “DHIS_BMI4” group.

To unsubscribe from this group and stop receiving emails from it, send an email to dhis_bmi4+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Yes i think it is not working in PostgreSQL.

···

On Sat, Mar 29, 2014 at 10:27 PM, Manaf Abthul drmanaf79@gmail.com wrote:

Hi sumudu,

It is working, testing is successful on Version:2.14 Build revision:13826.

I have checked for number of Asthma patient less than 12yrs. The age attribute is patient attribute.

Os: Win7

MySQL database

On 29 March 2014 19:04, sumudu weerasinghe sumuduw00@gmail.com wrote:

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 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]; 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

You received this message because you are subscribed to the Google Groups “DHIS_BMI4” group.

To unsubscribe from this group and stop receiving emails from it, send an email to dhis_bmi4+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.