dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Would like to hear what the devs have to say, but maybe something like...

1) Delete the primary key minmaxdataelementid
2) Execute

INSERT INTO minmaxdataelement
SELECT a.organisationunitid, b.dataelementid, 0 as minvalue, 99 as
maxvalue, 'TRUE'::boolean as generated
FROM (SELECT DISTINCT organisationunitid FROM organisationunit) a
CROSS JOIN
(SELECT DISTINCT dataelementid FROM dataelement) b

Knut said that that the DISTINCT elements should probably not be
there, but just in case...

3) Recreate the primary key minmaxdatelementid with a serial datatype
to give you a serial list of integers in this field.
4) Remove the default value of the primary key and set it back to a
plain integer data type.
5) Add the primary key constraint again to the minmaxdataelemntid.


This procedure will not take into account particular elements that
only certain data organisationunits should be submitting. So there
would likely be many min-max elements that would be populated into
this table that would actually not be necessary. An example would be
that districts that do not report on a particular dataelement should
not have a restriction on the min-max value if the actual reporting is
done at a lower level.

Seems like a hack to me, but perhaps it might work. It might be better
to select from the organisationunitstructure table, for a particular
level, instead of the entire organisationunit table. I would make a
backup of the DB obviously before I tried any of this. :)

Regards,
JPP


On Tue, May 19, 2009 at 5:06 PM,  <johansa@xxxxxxxxxx> wrote:
> Hi,
>
> In Sierra Leone, none of the orgunits have defined min-max ranges yet, and
> the historical data is too poor to be used for calculation of such ranges
> yet. Still, there have been some instances of really bad typos, such as
> 775 instead of 75. Most entries (maybe around 98%) are below 100, so as a
> temporary shield we would like to set default ranges as 0-99 (to catch all
> three-digit entries). Any clue on how to do this? Would running a query
> directly on the database solve it, and what would that query look like?
> (sorry, but not good at SQL)
>
> Johan
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>