dhis2-users team mailing list archive

[Gayan Yasantha <gayanyp@xxxxxxxxx>]

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
Exceptionin 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
<http://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




On Sat, Mar 8, 2014 at 8:54 AM, Gayan Yasantha <gayanyp@xxxxxxxxx> 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
>
>
> On Fri, Mar 7, 2014 at 11:54 AM, sumudu weerasinghe <sumuduw00@xxxxxxxxx>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  * ?
>>
>>
>>
>>
>> On Thu, Mar 6, 2014 at 9:44 PM, Tran Chau <tran.hispvietnam@xxxxxxxxx>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@xxxxxxxxx
>>>
>>>
>>> On Thu, Mar 6, 2014 at 10:13 PM, sumudu weerasinghe <sumuduw00@xxxxxxxxx
>>> > 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@xxxxxxxxx> 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@xxxxxxxxx
>>>>> > 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@xxxxxxxxx
>>>>>>
>>>>>>
>>>>>> On Sun, Mar 2, 2014 at 1:11 PM, sumudu weerasinghe <
>>>>>> sumuduw00@xxxxxxxxx> 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
>>>>>>>
>>>>>>>
>>>>>>> * 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Fri, Feb 28, 2014 at 2:57 PM, Tran Chau <
>>>>>>> tran.hispvietnam@xxxxxxxxx> 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@xxxxxxxxx
>>>>>>>>
>>>>>>>>
>>>>>>>> On Tue, Feb 25, 2014 at 3:07 PM, sumudu weerasinghe <
>>>>>>>> sumuduw00@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-users
>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Gayan Yasantha Piyasena
>



-- 
Gayan Yasantha Piyasena

Attachment: catalina.out
Description: Binary data