dhis2-users team mailing list archive

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

Dear Ant, Jason,

Thank you for your support. Those scripts are all responding to what I
need! Many thanks!

Best,

Tantely.

On Wed, Jan 11, 2017 at 9:43 AM, Ant Snyman <ant@xxxxxxxx> wrote:

> Thanks Jason,
> Nice output and the advantage is you do not have to join to the resource
> table _orgunitstructure. It is quite a complex query though, but still a
> nice example of using arrays and the path column.
> Thanks
> Ant
>
>
> On 11 January 2017 at 07:18, Jason Pickering <jason.p.pickering@xxxxxxxxx>
> wrote:
>
>> Here is another piece of SQL which will transform the "path" to names.
>>
>> You can then join this clause with your organisation unit table (or
>> whatever) on the organisationunitid property. It presents all names in a
>> single column, as opposed to the multiple columns in Ant's example
>>
>> SELECT z.organisationunitid,
>> array_to_string(array_agg(z.name
>> ORDER BY z.rn),'/') AS path
>> FROM
>> (SELECT y.organisationunitid,
>> ou.name,
>> y.elem,
>> y.rn
>> FROM organisationunit ou
>> INNER JOIN
>> (SELECT *,
>> x.path[x.rn] AS elem
>> FROM
>> (SELECT * ,
>> generate_subscripts(arr.path,1) AS rn
>> FROM
>> (SELECT organisationunitid,
>> string_to_array(substring(path
>> FROM 2), '/') AS path
>> FROM organisationunit) arr) x) y ON y.elem = ou.uid) z
>> GROUP BY organisationunitid;
>>
>> Regards,
>> Jason
>>
>>
>> On Wed, Jan 11, 2017 at 6:14 AM, Ant Snyman <ant@xxxxxxxx> wrote:
>>
>>> ....I should also add that by changing the where clause you can use this
>>> code for any level, for example if you change it to:
>>>
>>> where ous.level = 6 you will only get the structure for your level 6
>>> orgunits
>>>
>>> Regards
>>>
>>> Ant Snyman
>>>
>>>
>>> On 11 January 2017 at 07:05, Ant Snyman <ant@xxxxxxxx> wrote:
>>>
>>>> Hi Tantely,
>>>>
>>>> Try the code below. I normally exclude OU1 in the list because that is
>>>> generally the country code and just makes the report even more wide.
>>>>
>>>> Regards
>>>>
>>>> Ant Snyman
>>>>
>>>>
>>>> SELECT ous.level,
>>>> ou2.name as ou2_name, ou2.shortname as ou2_shortnamme, ou2.uid as
>>>> ou2_uid, ou2.code as ou2_code, ou2.organisationunitid as
>>>> ou2_organisationunitid,
>>>> ou3.name as ou3_name, ou3.shortname as ou3_shortnamme, ou3.uid as
>>>> ou3_uid, ou3.code as ou3_code, ou3.organisationunitid as
>>>> ou3_organisationunitid,
>>>> ou4.name as ou4_name, ou4.shortname as ou4_shortnamme, ou4.uid as
>>>> ou4_uid, ou4.code as ou4_code, ou4.organisationunitid as
>>>> ou4_organisationunitid,
>>>> ou5.name as ou5_name, ou5.shortname as ou5_shortnamme, ou5.uid as
>>>> ou5_uid, ou5.code as ou5_code, ou5.organisationunitid as
>>>> ou5_organisationunitid,
>>>> ou6.name as ou6_name, ou6.shortname as ou6_shortnamme, ou6.uid as
>>>> ou6_uid, ou6.code as ou6_code, ou6.organisationunitid as
>>>> ou6_organisationunitid
>>>> FROM organisationunit ou
>>>> INNER JOIN _orgunitstructure ous ON ou.organisationunitid =
>>>> ous.organisationunitid
>>>> LEFT OUTER JOIN organisationunit ou2 on ous.idlevel2 =
>>>> ou2.organisationunitid
>>>> LEFT OUTER JOIN organisationunit ou3 on ous.idlevel3 =
>>>> ou3.organisationunitid
>>>> LEFT OUTER JOIN organisationunit ou4 on ous.idlevel4 =
>>>> ou4.organisationunitid
>>>> LEFT OUTER JOIN organisationunit ou5 on ous.idlevel5 =
>>>> ou5.organisationunitid
>>>> LEFT OUTER JOIN organisationunit ou6 on ous.idlevel6 =
>>>> ou6.organisationunitid
>>>> where ous.level > 1
>>>> order by ous.level, ou2.name, ou3.name, ou4.name, ou5.name, ou6.name
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On 10 January 2017 at 23:56, Raminosoa Rabemanantsoa, Tantely <
>>>> traminosoa@xxxxxxxxxx> wrote:
>>>>
>>>>> Dear Community,
>>>>>
>>>>> I am trying to output a list of all the orgUnits from the SQLView
>>>>> (DHIS2 2.24). I would like to have all the information of all the levels of
>>>>> the orgUnits for each of the  6 levels implemented. I would like to have a
>>>>> result in the following form:
>>>>>
>>>>> orgUnitName (level6), orgUnitName (level5), orgUnitName (level4),
>>>>> orgUnitName (level3), orgUnitName (level2), orgUnitName (level1)
>>>>>
>>>>> I have used the following code to output all the information of the
>>>>> level 6:
>>>>>
>>>>> SELECT organisationunit.organisationunitid, organisationunit.uid,
>>>>> organisationunit.code, organisationunit.name,
>>>>> organisationunit.shortname FROM public.organisationunit,
>>>>> public._orgunitstructure WHERE organisationunit.organisationunitid =
>>>>> _orgunitstructure.idlevel6 AND organisationunit.code is not null;
>>>>>
>>>>>
>>>>> How should I update this code in order to pull out the desired output?
>>>>>
>>>>> Thank you for your support!
>>>>>
>>>>> Best,
>>>>>
>>>>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> *Ant Snyman*
>>>>
>>>> *Cell: 0824910449 <08-249%20104%2049>*
>>>>
>>>> *Landline: 028 2713242*
>>>>
>>>>
>>>> Health Information Systems Program - SA
>>>>
>>>
>>>
>>>
>>> --
>>>
>>> *Ant Snyman*
>>>
>>> *Cell: 0824910449 <08-249%20104%2049>*
>>>
>>> *Landline: 028 2713242*
>>>
>>>
>>> Health Information Systems Program - SA
>>>
>>> *This message and any attachments are subject to a disclaimer published
>>> at http://www.hisp.org/policies.html#comms_disclaimer
>>> <http://www.hisp.org/policies.html#comms_disclaimer>.  Please read the
>>> disclaimer before opening any attachment or taking any other action in
>>> terms of this electronic transmission.  If you cannot access the
>>> disclaimer, kindly send an email to disclaimer@xxxxxxxx
>>> <disclaimer@xxxxxxxx> and a copy will be provided to you. By replying to
>>> this e-mail or opening any attachment you agree to be bound by the
>>> provisions of the disclaimer.*
>>>
>>> _______________________________________________
>>> 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
>>>
>>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049 <+46%2076%20414%2070%2049>
>>
>
>
>
> --
>
> *Ant Snyman*
>
> *Cell: 0824910449*
>
> *Landline: 028 2713242*
>
>
> Health Information Systems Program - SA
>
> *This message and any attachments are subject to a disclaimer published at
> http://www.hisp.org/policies.html#comms_disclaimer
> <http://www.hisp.org/policies.html#comms_disclaimer>.  Please read the
> disclaimer before opening any attachment or taking any other action in
> terms of this electronic transmission.  If you cannot access the
> disclaimer, kindly send an email to disclaimer@xxxxxxxx
> <disclaimer@xxxxxxxx> and a copy will be provided to you. By replying to
> this e-mail or opening any attachment you agree to be bound by the
> provisions of the disclaimer.*
>

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