SQL View to output all levels of orgUnits

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.

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](http://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](http://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](http://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](http://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](http://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

This message and any attachments are subject to a disclaimer published at 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@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.

···

On 10 January 2017 at 23:56, Raminosoa Rabemanantsoa, Tantely traminosoa@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:

How should I update this code in order to pull out the desired output?

Thank you for your support!

Best,

Tantely

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;

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@lists.launchpad.net

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

…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

This message and any attachments are subject to a disclaimer published at 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@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.

···

On 11 January 2017 at 07:05, Ant Snyman ant@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 traminosoa@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:

How should I update this code in order to pull out the desired output?

Thank you for your support!

Best,

Tantely

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;

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@lists.launchpad.net

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

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 ant@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

This message and any attachments are subject to a disclaimer published at 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@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-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On 11 January 2017 at 07:05, Ant Snyman ant@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

Ant Snyman

Cell: 0824910449

Landline: 028 2713242

Health Information Systems Program - SA

On 10 January 2017 at 23:56, Raminosoa Rabemanantsoa, Tantely traminosoa@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:

How should I update this code in order to pull out the desired output?

Thank you for your support!

Best,

Tantely

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;

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@lists.launchpad.net

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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

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

This message and any attachments are subject to a disclaimer published at 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@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.

···

On 11 January 2017 at 07:18, Jason Pickering jason.p.pickering@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

Regards,

Jason

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;

On Wed, Jan 11, 2017 at 6:14 AM, Ant Snyman ant@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

This message and any attachments are subject to a disclaimer published at 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@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-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On 11 January 2017 at 07:05, Ant Snyman ant@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

Ant Snyman

Cell: 0824910449

Landline: 028 2713242

Health Information Systems Program - SA

On 10 January 2017 at 23:56, Raminosoa Rabemanantsoa, Tantely traminosoa@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:

How should I update this code in order to pull out the desired output?

Thank you for your support!

Best,

Tantely

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;

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@lists.launchpad.net

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

Dear Ant, Jason,

Thank you for your support. Those scripts are all responding to what I need! Many thanks!

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.

···

On Wed, Jan 11, 2017 at 9:43 AM, Ant Snyman ant@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

This message and any attachments are subject to a disclaimer published at 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@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.

On 11 January 2017 at 07:18, Jason Pickering jason.p.pickering@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

Regards,

Jason

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;

Ant Snyman

Cell: 0824910449

Landline: 028 2713242

Health Information Systems Program - SA

On Wed, Jan 11, 2017 at 6:14 AM, Ant Snyman ant@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

This message and any attachments are subject to a disclaimer published at 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@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-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On 11 January 2017 at 07:05, Ant Snyman ant@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

Ant Snyman

Cell: 0824910449

Landline: 028 2713242

Health Information Systems Program - SA

On 10 January 2017 at 23:56, Raminosoa Rabemanantsoa, Tantely traminosoa@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:

How should I update this code in order to pull out the desired output?

Thank you for your support!

Best,

Tantely

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;

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@lists.launchpad.net

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