dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

On 28 April 2014 22:37, Adebusoye Anifalaje <busoye@xxxxxxxx> wrote:

> Thanks Ola,
>
> Really useful comments.
>
> I tried the Enrolment Query on another database, it returned accurate
> values but I got the “column p.name does not exist” error when I tried to
> get more information on the aggregation. This database was configured with
> 2.15 from the beginning.
>

Yes, that is a bug in the code related to the query being created with "
p.name", so doesn't matter which database your on. Shouldn't effect your
aggregation results though as this is the query to list the detailed events
contributing to the aggregated number. Should be fixed soon yes.


>
> Thanks for the tip on using the query directly in the database. I remember
> seeing this error before on 2.13 and was resolved eventually. Hope the devs
> can do the same with this quickly.
>
>
> Cheers.
>
> Busoye
>
>
>
>
> On 28 Apr 2014, at 13:31, Ola Hodne Titlestad <olati@xxxxxxxxxx> wrote:
>
> Hi Busoye,
>
> Thanks for reporting this in such detail.
>
> Some comments in-line.
>
> On 28 April 2014 11:58, <busoye@xxxxxxxx> wrote:
>
>> Hi,
>>
>> Not quite sure if this is a bug so I am testing out on this forum before
>> reporting.
>>
>> We have configured a multiple event tracker database to register clients
>> (pregnant women) through the web-api. There are two enrolments used as test
>> data at the moment.
>>
>> The first set of aggregation queries should be fairly straightforward to
>> count the number of enrolments (aggregate data elements have been created,
>> included in data sets and assigned to orgunits).
>>
>> In the aggregate query builder, I have chosen "Number of Tracked
>> Entities", selected the program and used the program tab to define the
>> condition. For program properties, I chose "Program Enrollment" and in the
>> first instance the condition specified was only "Program Enrollment". The
>> manual aggregation returned "no values".
>
>
> That's actually the correct way to set up the aggregation query as far as
> I know. I tried the same on another database and got the expected results.
> As you say it could be something with the database. Maybe you could try the
> same approach on another database to see whether it works there, e.g. give
> it a try on the online demo. Was this database recently upgraded from
> version 2.14, or has this been used with 2.15 or 2.15 snapshot since you
> started on it? Something might have happened during the upgrade process
> from 2.14 to 2.15.
>
>
>> To explore another option, I tried "Program Enrollment ='true' but still
>> no results.
>>
>> To try a workaround, I used the attributes tab and selected one of the
>> mandatory attributes with a condition of "attribute_name is not null". Now
>> I got some very interesting results: a value of 2 for every single month in
>> the period specified! The value is correct but not for multiple periods; I
>> had used April 2012 to April 2014. Will not rule out that this has
>> something to do with our database setup. When you expand the aggregation
>> period to reveal the values and organisation unit, click on the "i" symbol
>> under "Operator", you get an exception. In the log, the error message is:
>>
>
> So your aggregated data element is in a data set with a monthly period
> type?
> I can see that your query (that failed) doesn't have any filters on
> period. That might be the reason for getting the save value for all the
> periods.
> So, that sounds like a bug.
>
>
>>
>> * ERROR 2014-04-28 10:02:56,281 Error while executing action
>> (ExceptionInterceptor.java [http-apr-8080-exec-6])
>> org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad
>> SQL grammar [SELECT p.name FROM  programinstance as pi INNER JOIN
>> trackedentityinstance p on p.trackedentityinstanceid=pi.trackedentityinstanceid
>> INNER JOIN organisationunit ou ON ou.organisationunitid=p.organisationunitid
>>  WHERE  EXISTS (   SELECT * FROM trackedentityattributevalue _pav  WHERE
>> _pav.trackedentityinstanceid=pi.trackedentityinstanceid  AND
>> _pav.trackedentityattributeid=8818 AND _pav.value  is not null ) ];
>> nested exception is org.postgresql.util.PSQLException: ERROR: column
>> p.name does not exist.
>>
>>
> I also got this. There is a bug with the query here, as you can see column
>  p.name does not exist. If you want to explore the results now you can
> run the sql query above directly on your database and just replace "select
>  p.name" with "select p.*" - that will give all the tracked entity
> instances (persons in your case) that meets the aggregation criteria.
>
> So you can use that query if you want to debug further what is happening
> with your aggregation queries.
>
> I'll do some more testing on my side as well, and I hope that the devs can
> have a look at these bugs quickly.
>
> Ola
> ----------
>
>
> Moving away from Program Attributes, I tried using the "Visited selected
>> program stage" condition. When The Aggregator Operator is "Number of
>> Tracked Entity", which is the most appropriate in this case, I get the
>> error above. However, when I change to number of visits, this works fine
>> but the log shows information that there is a data type mismatch i.e. not
>> integer or number. I changed the aggregate data element to text and the
>> aggregation value I got was "$value" This one seems like a bug to me.
>>  actual aggregation
>>
>>
>> Please find some screenshots attached to help illustrate the issues I
>> have highlighted.
>>
>>
>> Any help of input would be appreciated. I have given lots of detail here
>> to cover a spectrum of issues, I don't mind providing more specific
>> information into aspects that may help illuminate what the problems are.
>>
>> Best regards,
>>
>> Busoye
>>
>>
>>
>> Version:2.15
>> Build revision:14960
>>
>>
>>
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>
>
>