dhis2-devs team mailing list archive

[Ngoc Thanh Nguyen <thanh.hispvietnam@xxxxxxxxx>]

On Wed, May 20, 2009 at 3:13 AM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> 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,  <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<https://launchpad.net/%7Edhis2-devs>
> > Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> > 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     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> 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
--------------------