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 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
> 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 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
>> > 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
>> >> wrote:
>> >>>
>> >>> Am using psql. see attached if its correct loading procedure.
>> >>>
>> >>> On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring
>> wrote:
>>
>> Just run everything in your PgAdmin SQL interface.
>>
>> On Thu, Jan 25, 2018 at 1:22 PM, moses mwale
>> 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
>> > 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
>> >>>
