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...@gmail.com> 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 <bobjolli...@gmail.com> > 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...@gmail.com> 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.picker...@gmail.com> 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...@gmail.com> >> >> wrote: >> >>> >> >>> Am using psql. see attached if its correct loading procedure. >> >>> >> >>> On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring <knu...@gmail.com> >> wrote: >> >>>> >> >>>> Just run everything in your PgAdmin SQL interface. >> >>>> >> >>>> On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <isoftcom...@gmail.com> >> >>>> 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.picker...@gmail.com> 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...@gmail.com> >> >>>>>> 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...@gmail.com> >> >>>>>>> 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.picker...@gmail.com> 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" <knu...@gmail.com> >> 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...@gmail.com> >> 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@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 >> >>>>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> -- >> >>>>>>>> developer_lusaka_systems >> >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> -- >> >>>>>>> developer_lusaka_systems >> >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> -- >> >>>>>> Jason P. Pickering >> >>>>>> email: jason.p.picker...@gmail.com >> >>>>>> 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.picker...@gmail.com >> >> tel:+46764147049 >> > >> > >> > >> > >> > -- >> > developer_lusaka_systems >> > >> > _______________________________________________ >> > 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 >> > >> > > > > -- > developer_lusaka_systems > -- developer_lusaka_systems
_______________________________________________ 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