....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* > > *Landline: 028 2713242* > > > Health Information Systems Program - SA > -- *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
