dhis2-devs team mailing list archive

[moses mwale <isoftcom.ic@xxxxxxxxx>]

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring <knutst@xxxxxxxxx> wrote:

> Just run everything in your PgAdmin SQL interface.
>
> On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
> wrote:
>
>> okay nice thanks, is it to load user-defined functions the same way you
>> load stored procedures, by packaging the Java class or classes into a JAR
>> file and then loading the JAR file using the LOAD CLASSES statement?
>>
>> On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering <
>> jason.p.pickering@xxxxxxxxx> wrote:
>>
>>> Hi Moses,
>>>
>>> You need to load both of those functions into your database.
>>>
>>> 1) Script to delete an orgunit is here
>>> <https://github.com/dhis2/dhis2-utils/blob/master/resources/sql/delete_orgunit_with_data.sql>
>>> 2) Script to merge two orgunits is here
>>> <https://github.com/dhis2/dhis2-utils/blob/master/resources/sql/merge_orgunits.sql>
>>>
>>> After that, just call the function with
>>>
>>> SELECT merge_organisationunits(source_uid,dest_uid ,strategy);
>>>
>>> where `source_uid` is the UID of the organisation unit you want to get
>>> rid of, `dest_uid` is the UID of the organisation unit you want to keep and
>>> move data to
>>> and `strategy` is one of the following:
>>>
>>> SUM: Returns the sum of the values, when there is overlapping data.
>>> MAX: Returns the max of the values, when there is overlapping data.
>>> MIN: Returns the min of the values, when there is overlapping data.
>>> AVG: Returns the mean of the values, when there is overlapping data.
>>> LAST: Returns the last value entered, when there is overlapping data.
>>> FIRST: Returns the first vale entered, when there is overlapping data.
>>>
>>> Note that this only applies to numeric data. For other data (Boolean,
>>> text, etc) which cannot be aggregated numerically, the last value will
>>> always be taken.
>>>
>>> The merge script will not handle situations where you have overlapping
>>> tracker/event data, so you would need to figure out how to handle that
>>> yourself!
>>>
>>> Hope that helps to clarify and good luck!
>>>
>>> Regards,
>>> Jason Pickering
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Jan 25, 2018 at 5:30 AM, moses mwale <isoftcom.ic@xxxxxxxxx>
>>> wrote:
>>>
>>>> Hey Jason And Knut, thanks for the information, i have been trying to
>>>> understand the sql script shared and tried to execute but to no effect,
>>>> please can you help me understand where to place the source id and orgunit
>>>> ids in the script after several attempts of try its has became more
>>>> complex.
>>>>
>>>> I have attached a datavalue table and 1 duplicate orgunit to be merged.
>>>> please help me.
>>>>
>>>> On Mon, Jan 8, 2018 at 8:26 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
>>>> wrote:
>>>>
>>>>> Thanks very much, allow me to go through and implement the given
>>>>> knowledge
>>>>>
>>>>> On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering <
>>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>>
>>>>>> Hi Moses
>>>>>> We have some SQL scripts for this here.
>>>>>>
>>>>>> https://github.com/dhis2/dhis2-utils/blob/master/resources/s
>>>>>> ql/merge_orgunits.sql
>>>>>>
>>>>>> This script will not handle tracker data but could probably be
>>>>>> adapted fairly easily to do so.
>>>>>>
>>>>>> Regards,
>>>>>> Jason
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Jan 7, 2018 7:28 AM, "Knut Staring" <knutst@xxxxxxxxx> wrote:
>>>>>>
>>>>>>> Hi Moses,
>>>>>>> I would take a backup of the database and then do this with an sql
>>>>>>> script, just changing the sourceid (the database internal referent to the
>>>>>>> organisationunitid) in the datavalues table. But you may get blocked if the
>>>>>>> same period has been filled for both, in which case you may want to do a
>>>>>>> "NOT IN" or left join.
>>>>>>>
>>>>>>> Knut
>>>>>>>
>>>>>>> On Jan 7, 2018 10:29, "moses mwale" <isoftcom.ic@xxxxxxxxx> wrote:
>>>>>>>
>>>>>>>> Hey devs is it possible to merge facilities without losing data,
>>>>>>>> some admin accidentally created other duplicates units into the system.
>>>>>>>> anyone knows how its done?
>>>>>>>>
>>>>>>>> developer_lusaka_systems
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> developer_lusaka_systems
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> developer_lusaka_systems
>>>>
>>>
>>>
>>>
>>> --
>>> Jason P. Pickering
>>> email: jason.p.pickering@xxxxxxxxx
>>> tel:+46764147049 <+46%2076%20414%2070%2049>
>>>
>>
>>
>>
>> --
>> developer_lusaka_systems
>>
>
>
>
> --
> Knut Staring
>
> Department of Information, Evidence and Research
> World Health Organization, Geneva, Switzerland
> Office: +41 22 791 3683 <+41%2022%20791%2036%2083> Mob1: +33 6 4434 2931
> <+33%206%2044%2034%2029%2031> Mob2: +47 9188 0522
> Skype:     knutstar
>



-- 
developer_lusaka_systems

Attachment: merge.PNG
Description: PNG image