dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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.ic@xxxxxxxxx> 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.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/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" <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
>



-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049