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