Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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
> .
> 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 
> 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 
>>> 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 
 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 <
>> 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 
>> wrote:
>> 
>>  Just run everything in your PgAdmin SQL interface.
>> 
>>  On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <
>> isoftcom...@gmail.com>
>>  

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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  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
>> .
>> 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 
>> 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 
 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 
> 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 <
>>> isoftcom...@gmail.com>
>>> >> wrote:
>>> >>>
>>> >>> Am using psql. see attached if its correct loading 

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Jason Pickering]

Hi Moses,

The version of the function looks to be a bit outdated. I just updated it
again in this commit
.
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  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 
>> 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 
>>> 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 <
> 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

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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 
> 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 
>> 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 <
 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
 >  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);
 >>
 

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Jason Pickering]

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  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 
> 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 

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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 

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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  >
> >> 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  >
> >>> 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
>   wrote:
> >
> > Hi Moses
> > We have some SQL scripts for this here.
> >
> >
> > 

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Bob Jolliffe]

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 
>> 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 
>>> 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
  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"  wrote:
>>
>> Hi Moses,
>> I would take a backup of the database and then do this with an sql
>> script, just 

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Jason Pickering]

Hi again Moses,
The function itself should not affect anything, but its never a good idea
to do these things directly on a production database without extensive
testing.

Note that this function WILL remove data depending on whether its
overlapping. So, if you have two data values for the same period/data
element/cat option combo/attribute option combo , one of these is going to
be lost or at least affected in some way depending on the merge strategy
you chose. If there is no overlapping data, then there should not be any
data lost, but like I said, be sure you test it very carefully before doing
this on a live system.

Regards,
Jason


On Thu, Jan 25, 2018 at 2:05 PM, 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 <
> 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 
>> 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 <
> 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 
>> 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 
>>> 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
>
>

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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 
> 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 <
 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 
> 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 
>> 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"  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"  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?
>>

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Jason Pickering]

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 <
>>> 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 
 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 
> 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"  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"  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: 

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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 <
>> 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 
>>> 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 
 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"  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"  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 <+46%2076%20414%2070%2049>
>>>

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Knut Staring]

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 <
> 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 
>> 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 
>>> 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"  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"  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 <+46%2076%20414%2070%2049>
>>
>
>
>
> --
> 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

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Jason Pickering]

Hi Moses

These are simply Postgresql functions, which you load and execute directly
in the database. These are completely external of DHIS2, so you will need
to do this operation directly in the database environment.

Regards
Jason


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 <
> 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 
>> 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 
>>> 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"  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"  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 <+46%2076%20414%2070%2049>
>>
>
>
>
> --
> developer_lusaka_systems
>



-- 
Jason P. Pickering
email: jason.p.picker...@gmail.com

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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 
> 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 
>> 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"  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"  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 <+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

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Jason Pickering]

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  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  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"  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"  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
___
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

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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  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"  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"  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
___
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

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[moses mwale]

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"  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"  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
___
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

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Jason Pickering]

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"  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"  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
>
>
___
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

Re: [Dhis2-devs] merging facilities (org-units) without losing data

[Knut Staring]

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