Would like to hear what the devs have to say, but maybe something like…
-
Delete the primary key minmaxdataelementid
-
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…
- Recreate the primary key minmaxdatelementid with a serial datatype
to give you a serial list of integers in this field.
- Remove the default value of the primary key and set it back to a
plain integer data type.
- 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
Hi Jason,
You may compare to this
Okies, why dont you try this:
- if you want all dataelements have ranges
insert into minmaxdataelement(sourceid, dataelementid, minvalue, maxvalue) select organisationunitid, dataelementid, 0, 99 from organisationunit, dataelement
- if you only want those dataelements belonging to datasets assigned to certain orgunit
insert into minmaxdataelement(sourceid, dataelementid, minvalue, maxvalue)
select o.organisationunitid, dm.dataelementid, 0, 99
from organisationunit o inner join datasetsource ds on o.organisationunitid = ds.sourceid
inner join datasetmembers dm on dm.datasetid = ds.datasetid
remember to truncate minmaxdataelement table before inserting.
Thanh
···
On Wed, May 20, 2009 at 3:13 AM, Jason Pickering jason.p.pickering@gmail.com wrote:
On Tue, May 19, 2009 at 5:06 PM, johansa@ifi.uio.no 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
–
Best regards,
Thanh
Center for HIS development
957, 3/2 st., Dist 11, Hochiminh city
Tel: 08-956 0150
Cell: 098 221 8623