dhis2-devs team mailing list archive

[moses mwale <isoftcom.ic@xxxxxxxxx>]

Hello Jason, Knut and Bob

I did according to your per instruction, i had a dump or copy of the db, i
created a test db with it and loaded the functions then called using the
shared query

dhis2_test=> SELECT merge_organisationunits(37442, 24872 ,SUM);
ERROR:  column "sum" does not exist
LINE 1: SELECT merge_organisationunits(37442, 24872 ,SUM);
                                                     ^
That's the error i receive, what is wrong?

thanks in advance.

On Thu, Jan 25, 2018 at 3:32 PM, moses mwale <isoftcom.ic@xxxxxxxxx> wrote:

> Thanks all you are really help i appreciate so much. let me do test first
> on the backed up db if satisfied will run the merge on the Prod. Thanks.
>
> On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
> wrote:
>
>> If you really must work on your production database directly then at
>> the very least you should shutdown the dhis2 instance while you make
>> changes.  Naturally you want to keep that down time to a minimum.
>>
>> So I would suggest a sequence of:
>>
>> (i) make a copy of the database to test these procedures
>> (ii) test out all of this sql stuff against your db copy (making sure
>> you keep your sql in files so you can rerun against production)
>> Once you are happy you can do what you need to do quickly and correctly,
>> (iii) shutdown the dhis2 instance
>> (iv) make a backup of database (again)
>> (v) run the sql
>> (vi) restart dhis2 instance
>>
>> Stay safe.
>>
>> On 25 January 2018 at 13:05, moses mwale <isoftcom.ic@xxxxxxxxx> wrote:
>> > This is live (Production) db created a backup. if something goes wrong
>> will
>> > revert. But the function itself doesnt affect anything right? what am
>> going
>> > to write now will i guess by merging duplicate orgs right?
>> >
>> > On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering
>> > <jason.p.pickering@xxxxxxxxx> wrote:
>> >>
>> >> That looks correct.
>> >>
>> >> It is of course worth saying, that you should not attempt to perform
>> this
>> >> procedure on your production system, unless you have thoroughly tested
>> it in
>> >> a development environment!
>> >>
>> >> Regards,
>> >> Jason
>> >>
>> >>
>> >>
>> >> On Thu, Jan 25, 2018 at 1:55 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
>> >> wrote:
>> >>>
>> >>> 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
>> >>>>>> 2) Script to merge two orgunits is here
>> >>>>>>
>> >>>>>> 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/
>> sql/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
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>> --
>> >>>>> developer_lusaka_systems
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>> --
>> >>>> Knut Staring
>> >>>>
>> >>>> Department of Information, Evidence and Research
>> >>>> World Health Organization, Geneva, Switzerland
>> >>>> Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
>> >>>> Skype:     knutstar
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> --
>> >>> developer_lusaka_systems
>> >>
>> >>
>> >>
>> >>
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@xxxxxxxxx
>> >> tel:+46764147049
>> >
>> >
>> >
>> >
>> > --
>> > 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
>> >
>>
>
>
>
> --
> developer_lusaka_systems
>



-- 
developer_lusaka_systems