dhis2-devs team mailing list archive

[Ant Snyman <ant@xxxxxxxx>]

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