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/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" <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
_______________________________________________ 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