On Wed, May 20, 2009 at 3:13 AM, Jason Pickering < [email protected]> wrote:
> 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 > 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 Tue, May 19, 2009 at 5:06 PM, <[email protected]> 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<https://launchpad.net/%7Edhis2-devs> > > Post to : [email protected] > > Unsubscribe : > > https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> > > More help : https://help.launchpad.net/ListHelp > > > > _______________________________________________ > Mailing list: > https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> > Post to : [email protected] > Unsubscribe : > https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-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 --------------------
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
