dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Just forwarding a query I had from Andrew to the list.  I have observed
this change in the _organisationgroupsetstructure table which I think might
cause some grief to those who have written many direct queries against
resource tables.  Is it just us noticing this?  Was there some change
recently in the way resource table columns are named (RW just upgraded
recently to 2.13)?

---------- Forwarded message ----------
From: Bob Jolliffe <bobjolliffe@xxxxxxxxx>
Date: 21 November 2013 21:57
Subject: Re: Fw: Cron <dhis@hmisdb> psql -c 'select
materialize_facility_views()' hmis
To: Muhire Andrew <muhireandrew@xxxxxxxxx>
Cc: Randy Randy <rwilson@xxxxxxx>


That is very odd.  It seems with the upgrade to 2.13 the case of the
fieldname has been changed from "type" to "Type".  And presumably the same
with ownership.  I guess this has the effect of breaking many queries.
 Some examples:

hmis=# select organisationunitname, type from
_organisationunitgroupsetstructure limit 10;
ERROR:  column "type" does not exist
LINE 1: select organisationunitname, "type" from _organisationunitgr...

BUT

hmis=# select organisationunitname, "Type" from
_organisationunitgroupsetstructure limit 10;
  organisationunitname  |      Type
------------------------+----------------
 A La Source DISP       | Dispensary
 ABEF/Karongi Clinic    | Medical Clinic
 ADA CLIN               | Medical Clinic
 Afak DISP              | Dispensary
 Afya DISP              | Dispensary
 AHA Clinic             | Dispensary
 Amahoro Disp           | Dispensary
 Amani DISP             | Dispensary
 Amizero (Kacyiru) DISP | Dispensary
 Amizero (Kamonyi) DISP | Dispensary

That is a pain.  Probably you have to change the materialized views to use
"Type", "Ownership" etc (the quotes are significant).  This also messes up
my resourcemapper query :-(

Bob



On 21 November 2013 18:50, Muhire Andrew <muhireandrew@xxxxxxxxx> wrote:

>
>  Dear Bob, I don't know if i am right but this is a sign that the datamart
> failed to finish aggregating these tables. Is it right Bob and Randy? check
> on Materialized quiries below!
>
> ------------------------------
>
>
>
> * Muhire Andrew*
> * Ministry of Health / HMIS*
> *""A mind is a terrible thing to waste""*
>
> ***********************************
>
>  ** Cell:(+25)0788436150**
> *** Twitter :andrewmuhire***
> **** skype:muhire_andrew****
> *muhireandrew@xxxxxxxxx <muhireandrew@xxxxxxxxx>*
>
>
>   On Thursday, November 21, 2013 7:58 PM, Cron Daemon <
> root@xxxxxxxxxxxxxxxxx> wrote:
>  ERROR:  column _organisationunitgroupsetstructure.ownership does not
> exist
> LINE 5:  _organisationunitgroupsetstructure.ownership,
>         ^
> QUERY:
> INSERT INTO _view_healthfacilityhierarchy
> SELECT _organisationunitgroupsetstructure.organisationunitid,
> _organisationunitgroupsetstructure.organisationunitname,
> _organisationunitgroupsetstructure.ownership,
> "O5".name AS sector, "O6".code AS fosaid,
> _organisationunitgroupsetstructure.type,
> _orgunitstructure.level,
> "O4".name AS subdistrict,
> "O3".name AS district,
> "O2".name AS province,
> "O6".coordinates AS gpscoordinates,
> "O6".openingdate
>   FROM _orgunitstructure
>   INNER JOIN _organisationunitgroupsetstructure ON
>   _organisationunitgroupsetstructure.organisationunitid =
> _orgunitstructure.organisationunitid
>   INNER JOIN organisationunit "O4" ON "O4".organisationunitid =
> _orgunitstructure.idlevel4
>   INNER JOIN organisationunit "O3" ON _orgunitstructure.idlevel3 =
> "O3".organisationunitid
>   INNER JOIN organisationunit "O2" ON "O2".organisationunitid =
> _orgunitstructure.idlevel2
> INNER JOIN organisationunit "O5" ON
> _orgunitstructure.idlevel5 = "O5".organisationunitid
>   INNER JOIN organisationunit "O6" ON
> _orgunitstructure.organisationunitid = "O6".organisationunitid
>   WHERE _orgunitstructure.level = 6;
> CONTEXT:  PL/pgSQL function materialize_facility_views() line 29 at
> EXECUTE statement
>
>
>