Hi
I would like to share an SQL statement that would help you list all your
orgunits (code, coordinates, facility name, district, region, etc.) It is
tricky to query the same column "organisationunit.name" twice. Here is the
query:
================================================
SELECT ou.code,
ou.name as facility,
ou.coordinates,
par.name as district,
ou.contactperson,
ou.phonenumber,
(SELECT part.name
FROM organisationunit ou
INNER JOIN _orgunitstructure ous ON ou.organisationunitid =
ous.organisationunitid
INNER JOIN organisationunit part ON ou.parentid = part.organisationunitid
INNER JOIN orgunitlevel oul ON ous.level = oul.level
WHERE (ou.name is not null) AND (ou.name=par.name) AND (ous.level=3)
) AS region
FROM organisationunit ou
INNER JOIN _orgunitstructure ous ON ou.organisationunitid =
ous.organisationunitid
INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid
INNER JOIN orgunitlevel oul ON ous.level = oul.level
WHERE ou.name is not null AND ous.level=4
ORDER BY Region, par.name, ou.code
=====================================================
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : [email protected]
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
