dhis2-devs team mailing list archive

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

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.ic@xxxxxxxxx> 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.pickering@xxxxxxxxx> 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.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 <+46%2076%20414%2070%2049>
>>
>
>
>
> --
> developer_lusaka_systems
>



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