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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 < >>> [email protected]> 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 : [email protected] >>>> 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 [email protected] >> <[email protected]> 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 : [email protected] >> Unsubscribe : https://launchpad.net/~dhis2-users >> More help : https://help.launchpad.net/ListHelp >> >> > > > -- > Jason P. Pickering > email: [email protected] > 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 [email protected] <[email protected]> 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 : [email protected] Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
