Alright thanks Jason on it. On Mon, Jan 29, 2018 at 1:10 PM, Jason Pickering < jason.p.picker...@gmail.com> wrote:
> Hi Moses, > > The version of the function looks to be a bit outdated. I just updated it > again in this commit > <https://github.com/dhis2/dhis2-utils/commit/0b5026c70c6f0117dad83c7215ca89395f271f18>. > Maybe you can try again with this new version and see if it works? > > Regards, > Jason > > > On Mon, Jan 29, 2018 at 11:25 AM, moses mwale <isoftcom...@gmail.com> > wrote: > >> wow some major progress i thought it was special sql. I now know its >> normal sql. >> >> dhis2_test=> SELECT merge_organisationunits('FfX6DGeVsJd', 'qqkYhasbAcf' >> ,'SUM'); >> ERROR: column "attributeoptioncomboid" is of type integer but expression >> is of type character varying >> LINE 6: value, >> ^ >> HINT: You will need to rewrite or cast the expression. >> QUERY: INSERT INTO datavalueaudit SELECT nextval('hibernate_sequence':: >> regclass), >> dataelementid, >> periodid, >> ( SELECT organisationunitid from organisationunit where uid = >> 'qqkYhasbAcf' ) as organisationunitid, >> categoryoptioncomboid, >> value, >> now()::timestamp without time zone, >> 'admin'::character varying(100) as modifiedby, >> 'MERGE_SOURCE'::character varying(255) as audittype, >> attributeoptioncomboid >> FROM datavalue where sourceid = ( SELECT organisationunitid >> from organisationunit where uid = 'FfX6DGeVsJd' ) >> CONTEXT: PL/pgSQL function >> merge_organisationunits(character,character,character >> varying) line 178 at EXECUTE >> >> if i change in the function will it work? >> >> On Mon, Jan 29, 2018 at 10:01 AM, Jason Pickering < >> jason.p.picker...@gmail.com> wrote: >> >>> Hi Moses, >>> >>> You need to use the UID of the organisation unit not their integer ID. >>> >>> You need to place string literals in single quotes. >>> >>> So, it should be something like >>> >>> SELECT merge_organisationunits('foo', 'bar' ,'SUM'); >>> >>> >>> Regards, >>> Jason >>> >>> >>> On Sun, Jan 28, 2018 at 5:28 PM, moses mwale <isoftcom...@gmail.com> >>> wrote: >>> >>>> 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 >>>> >>> >>> >>> >>> -- >>> Jason P. Pickering >>> email: jason.p.picker...@gmail.com >>> tel:+46764147049 <+46%2076%20414%2070%2049> >>> >> >> >> >> -- >> developer_lusaka_systems >> > > > > -- > Jason P. Pickering > email: jason.p.picker...@gmail.com > tel:+46764147049 <+46%2076%20414%2070%2049> > -- 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
