Thanks very much it has worked just analyzing the data now. You a savior. On Mon, Jan 29, 2018 at 1:20 PM, moses mwale <isoftcom...@gmail.com> wrote:
> 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 > -- 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