dhis2-devs team mailing list archive

[Juma Lungo <jlungo@xxxxxxxxx>]

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

=====================================================