dhis2-users team mailing list archive

["Wilson, Randy" <rwilson@xxxxxxx>]

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

-- 
*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.*