dhis2-users team mailing list archive

[Murod Latifov <mlatifov@xxxxxxxxx>]

Hi Jason,

Yes, it holds true in case if raw data being called. But query was with
aggregation elements. Technique I proposed will exactly remove none
convertible values from the query, thus there won't be cast exception. No
need to deploy heavy regex or stored procedure here, this is doable. Sample
code here:

select dv.dataelementid, categoryoptioncomboid, periodid, sum(cast (value
as int)) as val, dv.sourceid as orgunit, aggregationtype from datavalue dv
left join dataelement de
on dv.dataelementid = de.dataelementid
WHERE aggregationtype = 'sum' and domaintype='aggregate'
group by dv.dataelementid, dv.sourceid, categoryoptioncomboid, periodid,
aggregationtype

This should give an idea on how to manage avoiding these exceptions just
with plain SQL.

regards,
murod


On Fri, Nov 29, 2013 at 5:46 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Well, it is very hard to see what is going on without the entire query. My
> point here is there is no way to aggregate text, without some sort of
> procedure, so you cannot cast anything, nor can you sum it, because in the
> datavalue table, everything is stored as text. Just like with the
> analytics, you must first filter out all values which you think can be cast
> to a double with a regex, and then perform the sum/average/other
> aggregation operator.  If you just want to get the raw data values back,
> then there should be no aggregation anyway, thus, no need for the sum
> operator.
>
> Regards,
> Jason
>
>
>
>
>
> On Fri, Nov 29, 2013 at 2:39 PM, Murod Latifov <mlatifov@xxxxxxxxx> wrote:
>
>> I am afraid Jason, it won't work that way. In Postgres aggregating (SUM,
>> AVG) for varchar will give exception. There is no onbuilt type casting at
>> this point.
>>
>>
>> On Fri, Nov 29, 2013 at 5:09 PM, Jason Pickering <
>> jason.p.pickering@xxxxxxxxx> wrote:
>>
>>> You cannot cast text to a double so just remove that cast operator and
>>> you should be fine.
>>>
>>> --Sent from my mobile
>>> On Nov 29, 2013 1:16 PM, "Felix Obareh" <fobareh@xxxxxxxxxxxxxx> wrote:
>>>
>>>> Thank Jason,
>>>> I have decided to go with the SQL Query. I am using the SQL Query
>>>> example i the user manual together with the demo database for Sierra Leone.
>>>>
>>>> It is working fine for elements of integer type and i can run the query
>>>> from my pgAdmin and i get them fine. I have a problem with displaying data
>>>> elements of text and varchar type.
>>>>
>>>> Here is what am running as querry:-
>>>>
>>>> ------Trancated--------
>>>>
>>>> left outer join organisationunit Sublocation
>>>>   on (ous.idlevel6=sublocation.organisationunitid)
>>>> left outer join (
>>>>   *select sourceid, sum(cast(value as double precision)) as value*
>>>>   from datavalue
>>>>   where dataelementid=8305
>>>>
>>>>  group by sourceid) as *triggerstate* on
>>>> triggerstate.sourceid=ou.organisationunitid
>>>> where ous.level=7
>>>>
>>>> ****If i run the same query for a data element of date of text type or
>>>> yes/no
>>>> ***NB the triggerstate store a true or false value in the the datavalue
>>>> table
>>>>
>>>> *HERE is the SQL error i get*
>>>>
>>>> ERROR:  invalid input syntax for type double precision: "false"
>>>>
>>>> ********** Error **********
>>>>
>>>> ERROR: invalid input syntax for type double precision: "false"
>>>> SQL state: 22P02
>>>>
>>>>
>>>> ----------------------------------
>>>> Please assist
>>>>
>>>>
>>>> On Wed, Nov 27, 2013 at 5:44 PM, Jason Pickering <
>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>
>>>>> Hi Obare,
>>>>> The reason for this is because you will only be able to see the
>>>>> aggregated data in DHIS2. In your case, it does not seem you need the
>>>>> aggregated data, but instead the raw data. One way to do this is with a
>>>>> custom report, which would use a JDBC data source, and then an SQL query to
>>>>> retreive the data. You might need some paramaters like the period and the
>>>>> particular orgunit. You can read about custom reports in the DHIS2
>>>>> documentation. Another possible solution would be an SQL Query, which would
>>>>> allow you to pull out the raw data into a CSV file for subsequent analysis.
>>>>>
>>>>> Hope this helps.
>>>>>
>>>>> Regards,
>>>>> Jason
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Nov 27, 2013 at 2:34 PM, Felix Obareh <fobareh@xxxxxxxxxxxxxx>wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I a form similar to the Facility Assessment Form in the
>>>>>> demo.dhis2.org . I have done some data entry but i don't know how to
>>>>>> get the report for that form
>>>>>>
>>>>>> Among my Elements are:-
>>>>>>
>>>>>> 1) Date Facility was commissioned
>>>>>> 2) Facility state on Commissioning with options set of ( Ready,
>>>>>> Lacking Some Departments, Not ready)
>>>>>> 3) Date of Certification
>>>>>> 4) Date of Verification
>>>>>> 5) Source of Funds with option sets (Government, Donor)
>>>>>> 6) Is facility well maintained (Yes/No)
>>>>>> 7) Key issues identified & suggested solutions (Text)
>>>>>> 8) Number of Practitioners in the Facility
>>>>>>
>>>>>> I can the above data on the entry form by i cant create a single
>>>>>> report about the facility. The pivot tables on show the value of element 8.
>>>>>> The value of the number of practitioners.
>>>>>>
>>>>>> Any assistance will be highly appreciated. I have three more Facility
>>>>>> based forms which i want to get reports for across the country
>>>>>>
>>>>>> Cheers
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> _______________________________________________
>>>>>> 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
>>>
>>>
>>
>