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 <a...@hisp.org> 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.picker...@gmail.com> > 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 <a...@hisp.org> 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 <a...@hisp.org> 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 < >>>> tramino...@mikolo.org> 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-us...@lists.launchpad.net >>>>> 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 disclai...@hisp.org >>> <disclai...@hisp.org> 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-us...@lists.launchpad.net >>> Unsubscribe : https://launchpad.net/~dhis2-users >>> More help : https://help.launchpad.net/ListHelp >>> >>> >> >> >> -- >> Jason P. Pickering >> email: jason.p.picker...@gmail.com >> 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 disclai...@hisp.org > <disclai...@hisp.org> 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.*
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp