dhis2-users team mailing list archive

["Raminosoa Rabemanantsoa, Tantely" <traminosoa@xxxxxxxxxx>]

Thank you Randy. I am going to check this postgresql.conf file to
troubleshoot this issue.

Best Regards.

On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy <rwilson@xxxxxxx> wrote:

> I think the settings are in postgresql.conf.
>
> You could check the following:
> statement_timeout - set it to 0 to disable it.
>
> I can't seem to find the command limited the number of records a query can
> return.  I suppose it varies with the size of the record.
>
> Randy
>
>
>
> On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely <
> traminosoa@xxxxxxxxxx> wrote:
>
>> Hi Randy,
>>
>> The SQL script is running fine after putting "limit 100" at the end of
>> the query. Thanks! This script is answering to my question and I would like
>> to customize it further. I am using SSH to connect to the remote server and
>> would like to ask you where should be located the postgres config file for
>> me to make some change on the size of the query limitation?
>>
>> Best Regards.
>>
>> On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy <rwilson@xxxxxxx> wrote:
>>
>>> Hi Tantely,
>>>
>>> Are you limiting the data to a single data set at a time?  You might try
>>> to limit the result set by putting "limit 100" at the end of the query.
>>> That way you can at least see if the query is working.  There are often
>>> limits set on the postgres server for the number of records that can be
>>> returned, especially on cloud servers.  You might need to change a
>>> parameter in the config.
>>>
>>> Randy
>>>
>>> On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely <
>>> traminosoa@xxxxxxxxxx> wrote:
>>>
>>>> Thank you Randy and Knut for the scripts. While I am running the sample
>>>> script and customizing with the dataset name, I am facing another issue
>>>> with a timed out connection due to a busy server. I cannot output any
>>>> result yet. Do you know if it is a server memory issue?
>>>>
>>>> Best Regards.
>>>>
>>>> Tantely.
>>>>
>>>> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy <rwilson@xxxxxxx> wrote:
>>>>
>>>>> Hi Tantely,
>>>>>
>>>>> Here is a sample query that will give you what I think you want:
>>>>> ________
>>>>> select dv.sourceid as organisationunitid,
>>>>> ou.name, ou.code as facilitycode, de.name, 'Vaccination' as
>>>>> datasetname,
>>>>> pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value
>>>>>
>>>>> from datavalue dv
>>>>> inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)
>>>>> inner join dataelement de on (dv.dataelementid=de.dataelementid)
>>>>> inner join period pe on (dv.periodid=pe.periodid)
>>>>> inner join _categoryoptioncomboname coc on
>>>>> (dv.categoryoptioncomboid=coc.categoryoptioncomboid)
>>>>> where dv.dataelementid in
>>>>> (select dataelementid from datasetmembers where datasetid in (
>>>>> select datasetid from dataset where name='Vaccination'))
>>>>> _____
>>>>>
>>>>> Just replace 'Vaccination' with the name of your dataset.  You can
>>>>> also create a variable for the dataset name  of your SQL view in the newer
>>>>> versions of DHIS-2.
>>>>>
>>>>> Good luck.
>>>>>
>>>>> On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring <knutst@xxxxxxxxx>
>>>>> wrote:
>>>>>
>>>>>> Hi Tantely,
>>>>>>
>>>>>> You are probably looking for the datasetmembers table and the
>>>>>> datasetsource table (confusingly and historically, orgunits used to be
>>>>>> referred to as sources, and this is still reflected in a few places).
>>>>>>
>>>>>> When you say data elements, I assume you mean data values? If so,
>>>>>> then something along the lines of the query below (though this didnt work
>>>>>> for me right now
>>>>>>
>>>>>> SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement
>>>>>> de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss
>>>>>>
>>>>>> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely <
>>>>>> traminosoa@xxxxxxxxxx> wrote:
>>>>>>
>>>>>>> Dear DHIS2 users,
>>>>>>>
>>>>>>>
>>>>>>> I would like to export all data elements related to all organization
>>>>>>> units linked to a data set (questionnaire) on a specific period through the
>>>>>>> SQL View. Does anyone have some idea of on which table (/tables) should I
>>>>>>> make the SQL select script in DHIS2? The reason is that I have already
>>>>>>> exported the data through the “data export” (csv) but it returned me data
>>>>>>> values along columns and I need to transpose in Excel each Organization
>>>>>>> Unit to have data values aligned by row.
>>>>>>>
>>>>>>>
>>>>>>> Thank you for your suggestions.
>>>>>>>
>>>>>>>
>>>>>>> Best Regards,
>>>>>>>
>>>>>>>
>>>>>>> Tantely
>>>>>>>
>>>>>>> *This message and its attachments are confidential and solely for
>>>>>>> the intended recipients. If received in error, please delete them and
>>>>>>> notify the sender via reply e-mail immediately.*
>>>>>>> _______________________________________________
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Knut Staring
>>>>>> Dept. of Informatics, University of Oslo
>>>>>> Norway: +4791880522
>>>>>> Skype: knutstar
>>>>>> http://dhis2.org
>>>>>>
>>>>>> _______________________________________________
>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Randy Wilson*
>>>>> *Team Leader: **Knowledge Management, Data Use and Research*
>>>>> Rwanda Health System Strengthening Activity
>>>>> Management Sciences for Health
>>>>> Rwanda-Kigali
>>>>> Direct: +250 788308835
>>>>> E-mail: rwilson@xxxxxxx
>>>>> Skype: wilsonrandy_us
>>>>> <http://www.msh.org/>
>>>>> Stronger health systems. Greater health impact.
>>>>> <https://www.facebook.com/ManagementSciencesForHealth>
>>>>> <https://twitter.com/MSHHealthImpact>
>>>>> <https://www.youtube.com/user/MSHHealthImpact>
>>>>> www.msh.org
>>>>>
>>>>
>>>
>>>
>>> --
>>> *Randy Wilson*
>>> *Team Leader: **Knowledge Management, Data Use and Research*
>>> Rwanda Health System Strengthening Activity
>>> Management Sciences for Health
>>> Rwanda-Kigali
>>> Direct: +250 788308835
>>> E-mail: rwilson@xxxxxxx
>>> Skype: wilsonrandy_us
>>> <http://www.msh.org/>
>>> Stronger health systems. Greater health impact.
>>> <https://www.facebook.com/ManagementSciencesForHealth>
>>> <https://twitter.com/MSHHealthImpact>
>>> <https://www.youtube.com/user/MSHHealthImpact>
>>> www.msh.org
>>>
>>
>>
>
>
> --
> *Randy Wilson*
> *Team Leader: **Knowledge Management, Data Use and Research*
> Rwanda Health System Strengthening Activity
> Management Sciences for Health
> Rwanda-Kigali
> Direct: +250 788308835
> E-mail: rwilson@xxxxxxx
> Skype: wilsonrandy_us
> <http://www.msh.org/>
> Stronger health systems. Greater health impact.
> <https://www.facebook.com/ManagementSciencesForHealth>
> <https://twitter.com/MSHHealthImpact>
> <https://www.youtube.com/user/MSHHealthImpact>
> www.msh.org
>



-- 
*Tantely Raminosoa*
*Data Officer*
Management Sciences for Health
Antananarivo Madagascar
Mobile: 0321180040
E-mail: traminosoa@xxxxxxxxxx
Skype: traminosoa
<http://www.msh.org/>
Stronger health systems. Greater health impact.
<https://www.facebook.com/ManagementSciencesForHealth>
<https://twitter.com/MSHHealthImpact>
<https://www.youtube.com/user/MSHHealthImpact>
www.msh.org

-- 
*This message and its attachments are confidential and solely for the 
intended recipients. If received in error, please delete them and notify 
the sender via reply e-mail immediately.*