Merging facilities (org-units) without losing data

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

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@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

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” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

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@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” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

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.

image

image

···

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

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@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

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@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

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 isoftcom.ic@gmail.com wrote:

okay nice thanks, is it to load user-defined functions the same way you load stored procedures, by packaging the Java class or classes into a JAR file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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

developer_lusaka_systems

On Thu, Jan 25, 2018 at 5:30 AM, moses mwale isoftcom.ic@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Just run everything in your PgAdmin SQL interface.

···

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@gmail.com wrote:

okay nice thanks, is it to load user-defined functions the same way you load stored procedures, by packaging the Java class or classes into a JAR file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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

developer_lusaka_systems

On Thu, Jan 25, 2018 at 5:30 AM, moses mwale isoftcom.ic@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

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

Am using psql. see attached if its correct loading procedure.

image

···

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@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?

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

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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

developer_lusaka_systems

On Thu, Jan 25, 2018 at 5:30 AM, moses mwale isoftcom.ic@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

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@gmail.com wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

developer_lusaka_systems

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@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?

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

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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

developer_lusaka_systems

On Thu, Jan 25, 2018 at 5:30 AM, moses mwale isoftcom.ic@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

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@gmail.com wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this procedure on your production system, unless you have thoroughly tested it in a development environment!

Regards,

Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com wrote:

Am using psql. see attached if its correct loading procedure.


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

developer_lusaka_systems

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@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?

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

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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

developer_lusaka_systems

On Thu, Jan 25, 2018 at 5:30 AM, moses mwale isoftcom.ic@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

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 isoftcom.ic@gmail.com wrote:

This is live (Production) db created a backup. if something goes wrong will revert. But the function itself doesnt affect anything right? what am going to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com wrote:

Am using psql. see attached if its correct loading procedure.


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

developer_lusaka_systems

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@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?

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

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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

developer_lusaka_systems

On Thu, Jan 25, 2018 at 5:30 AM, moses mwale isoftcom.ic@gmail.com wrote:

Hey Jason And Knut, thanks for the information, i have been trying to understand the sql script shared and tried to execute but to no effect, please can you help me understand where to place the source id and orgunit ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be merged. please help me.

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks very much, allow me to go through and implement the given knowledge

developer_lusaka_systems

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Jan 7, 2018 7:28 AM, “Knut Staring” knutst@gmail.com wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql script, just changing the sourceid (the database internal referent to the organisationunitid) in the datavalues table. But you may get blocked if the same period has been filled for both, in which case you may want to do a “NOT IN” or left join.

Knut


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

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data, some admin accidentally created other duplicates units into the system. anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

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@gmail.com> wrote:

This is live (Production) db created a backup. if something goes wrong will
revert. But the function itself doesnt affect anything right? what am going
to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering > <jason.p.pickering@gmail.com> wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this
procedure on your production system, unless you have thoroughly tested it in
a development environment!

Regards,
Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale <isoftcom.ic@gmail.com> >> wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring <knutst@gmail.com> wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <isoftcom.ic@gmail.com> >>>> wrote:

okay nice thanks, is it to load user-defined functions the same way you
load stored procedures, by packaging the Java class or classes into a JAR
file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering >>>>> <jason.p.pickering@gmail.com> wrote:

Hi Moses,

You need to load both of those functions into your database.

1) Script to delete an orgunit is here
2) Script to merge two orgunits is here

After that, just call the function with

SELECT merge_organisationunits(source_uid,dest_uid ,strategy);

where `source_uid` is the UID of the organisation unit you want to get
rid of, `dest_uid` is the UID of the organisation unit you want to keep and
move data to
and `strategy` is one of the following:

SUM: Returns the sum of the values, when there is overlapping data.
MAX: Returns the max of the values, when there is overlapping data.
MIN: Returns the min of the values, when there is overlapping data.
AVG: Returns the mean of the values, when there is overlapping data.
LAST: Returns the last value entered, when there is overlapping data.
FIRST: Returns the first vale entered, when there is overlapping data.

Note that this only applies to numeric data. For other data (Boolean,
text, etc) which cannot be aggregated numerically, the last value will
always be taken.

The merge script will not handle situations where you have overlapping
tracker/event data, so you would need to figure out how to handle that
yourself!

Hope that helps to clarify and good luck!

Regards,
Jason Pickering

On Thu, Jan 25, 2018 at 5:30 AM, moses mwale <isoftcom.ic@gmail.com> >>>>>> wrote:

Hey Jason And Knut, thanks for the information, i have been trying to
understand the sql script shared and tried to execute but to no effect,
please can you help me understand where to place the source id and orgunit
ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be
merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale <isoftcom.ic@gmail.com> >>>>>>> wrote:

Thanks very much, allow me to go through and implement the given
knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering >>>>>>>> <jason.p.pickering@gmail.com> wrote:

Hi Moses
We have some SQL scripts for this here.

dhis2-utils/resources/sql/merge_orgunits.sql at master · dhis2/dhis2-utils · GitHub

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@gmail.com> wrote:

Hi Moses,
I would take a backup of the database and then do this with an sql
script, just changing the sourceid (the database internal referent to the
organisationunitid) in the datavalues table. But you may get blocked if the
same period has been filled for both, in which case you may want to do a
"NOT IN" or left join.

Knut

On Jan 7, 2018 10:29, "moses mwale" <isoftcom.ic@gmail.com> wrote:

Hey devs is it possible to merge facilities without losing data,
some admin accidentally created other duplicates units into the system.
anyone knows how its done?

developer_lusaka_systems

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

--
developer_lusaka_systems

--
developer_lusaka_systems

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

--
developer_lusaka_systems

--
Knut Staring

Department of Information, Evidence and Research
World Health Organization, Geneva, Switzerland
Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
Skype: knutstar

--
developer_lusaka_systems

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

--
developer_lusaka_systems

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

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@gmail.com wrote:

If you really must work on your production database directly then at

the very least you should shutdown the dhis2 instance while you make

changes. Naturally you want to keep that down time to a minimum.

So I would suggest a sequence of:

(i) make a copy of the database to test these procedures

(ii) test out all of this sql stuff against your db copy (making sure

you keep your sql in files so you can rerun against production)

Once you are happy you can do what you need to do quickly and correctly,

(iii) shutdown the dhis2 instance

(iv) make a backup of database (again)

(v) run the sql

(vi) restart dhis2 instance

Stay safe.

On 25 January 2018 at 13:05, moses mwale isoftcom.ic@gmail.com wrote:

This is live (Production) db created a backup. if something goes wrong will

revert. But the function itself doesnt affect anything right? what am going

to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering

jason.p.pickering@gmail.com wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this

procedure on your production system, unless you have thoroughly tested it in

a development environment!

Regards,

Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@gmail.com

wrote:

okay nice thanks, is it to load user-defined functions the same way you

load stored procedures, by packaging the Java class or classes into a JAR

file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering

jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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@gmail.com

wrote:

Hey Jason And Knut, thanks for the information, i have been trying to

understand the sql script shared and tried to execute but to no effect,

please can you help me understand where to place the source id and orgunit

ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be

merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Thanks very much, allow me to go through and implement the given

knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering

jason.p.pickering@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” knutst@gmail.com wrote:

Hi Moses,

I would take a backup of the database and then do this with an sql

script, just changing the sourceid (the database internal referent to the

organisationunitid) in the datavalues table. But you may get blocked if the

same period has been filled for both, in which case you may want to do a

“NOT IN” or left join.

Knut

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data,

some admin accidentally created other duplicates units into the system.

anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems

Knut Staring

Department of Information, Evidence and Research

World Health Organization, Geneva, Switzerland

Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522

Skype: knutstar

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

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@gmail.com wrote:

Thanks all you are really help i appreciate so much. let me do test first on the backed up db if satisfied will run the merge on the Prod. Thanks.

On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

If you really must work on your production database directly then at

the very least you should shutdown the dhis2 instance while you make

changes. Naturally you want to keep that down time to a minimum.

So I would suggest a sequence of:

(i) make a copy of the database to test these procedures

(ii) test out all of this sql stuff against your db copy (making sure

you keep your sql in files so you can rerun against production)

Once you are happy you can do what you need to do quickly and correctly,

(iii) shutdown the dhis2 instance

(iv) make a backup of database (again)

(v) run the sql

(vi) restart dhis2 instance

Stay safe.

On 25 January 2018 at 13:05, moses mwale isoftcom.ic@gmail.com wrote:

This is live (Production) db created a backup. if something goes wrong will

revert. But the function itself doesnt affect anything right? what am going

to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering

jason.p.pickering@gmail.com wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this

procedure on your production system, unless you have thoroughly tested it in

a development environment!

Regards,

Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@gmail.com

wrote:

okay nice thanks, is it to load user-defined functions the same way you

load stored procedures, by packaging the Java class or classes into a JAR

file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering

jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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@gmail.com

wrote:

Hey Jason And Knut, thanks for the information, i have been trying to

understand the sql script shared and tried to execute but to no effect,

please can you help me understand where to place the source id and orgunit

ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be

merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Thanks very much, allow me to go through and implement the given

knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering

jason.p.pickering@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” knutst@gmail.com wrote:

Hi Moses,

I would take a backup of the database and then do this with an sql

script, just changing the sourceid (the database internal referent to the

organisationunitid) in the datavalues table. But you may get blocked if the

same period has been filled for both, in which case you may want to do a

“NOT IN” or left join.

Knut

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data,

some admin accidentally created other duplicates units into the system.

anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems

Knut Staring

Department of Information, Evidence and Research

World Health Organization, Geneva, Switzerland

Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522

Skype: knutstar

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

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@gmail.com wrote:

Hello Jason, Knut and Bob

I did according to your per instruction, i had a dump or copy of the db, i created a test db with it and loaded the functions then called using the shared query

dhis2_test=> SELECT merge_organisationunits(37442, 24872 ,SUM);
ERROR: column “sum” does not exist
LINE 1: SELECT merge_organisationunits(37442, 24872 ,SUM);
^

That’s the error i receive, what is wrong?

thanks in advance.

On Thu, Jan 25, 2018 at 3:32 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks all you are really help i appreciate so much. let me do test first on the backed up db if satisfied will run the merge on the Prod. Thanks.

developer_lusaka_systems

On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

If you really must work on your production database directly then at

the very least you should shutdown the dhis2 instance while you make

changes. Naturally you want to keep that down time to a minimum.

So I would suggest a sequence of:

(i) make a copy of the database to test these procedures

(ii) test out all of this sql stuff against your db copy (making sure

you keep your sql in files so you can rerun against production)

Once you are happy you can do what you need to do quickly and correctly,

(iii) shutdown the dhis2 instance

(iv) make a backup of database (again)

(v) run the sql

(vi) restart dhis2 instance

Stay safe.

On 25 January 2018 at 13:05, moses mwale isoftcom.ic@gmail.com wrote:

This is live (Production) db created a backup. if something goes wrong will

revert. But the function itself doesnt affect anything right? what am going

to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering

jason.p.pickering@gmail.com wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this

procedure on your production system, unless you have thoroughly tested it in

a development environment!

Regards,

Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@gmail.com

wrote:

okay nice thanks, is it to load user-defined functions the same way you

load stored procedures, by packaging the Java class or classes into a JAR

file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering

jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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@gmail.com

wrote:

Hey Jason And Knut, thanks for the information, i have been trying to

understand the sql script shared and tried to execute but to no effect,

please can you help me understand where to place the source id and orgunit

ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be

merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Thanks very much, allow me to go through and implement the given

knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering

jason.p.pickering@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” knutst@gmail.com wrote:

Hi Moses,

I would take a backup of the database and then do this with an sql

script, just changing the sourceid (the database internal referent to the

organisationunitid) in the datavalues table. But you may get blocked if the

same period has been filled for both, in which case you may want to do a

“NOT IN” or left join.

Knut

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data,

some admin accidentally created other duplicates units into the system.

anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems

Knut Staring

Department of Information, Evidence and Research

World Health Organization, Geneva, Switzerland

Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522

Skype: knutstar

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

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@gmail.com wrote:

Hi Moses,

You need to use the UID of the organisation unit not their integer ID.

You need to place string literals in single quotes.

So, it should be something like

SELECT merge_organisationunits(‘foo’, ‘bar’ ,‘SUM’);

Regards,

Jason

On Sun, Jan 28, 2018 at 5:28 PM, moses mwale isoftcom.ic@gmail.com wrote:

Hello Jason, Knut and Bob

I did according to your per instruction, i had a dump or copy of the db, i created a test db with it and loaded the functions then called using the shared query

dhis2_test=> SELECT merge_organisationunits(37442, 24872 ,SUM);
ERROR: column “sum” does not exist
LINE 1: SELECT merge_organisationunits(37442, 24872 ,SUM);
^

That’s the error i receive, what is wrong?

thanks in advance.


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Thu, Jan 25, 2018 at 3:32 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks all you are really help i appreciate so much. let me do test first on the backed up db if satisfied will run the merge on the Prod. Thanks.

developer_lusaka_systems

On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

If you really must work on your production database directly then at

the very least you should shutdown the dhis2 instance while you make

changes. Naturally you want to keep that down time to a minimum.

So I would suggest a sequence of:

(i) make a copy of the database to test these procedures

(ii) test out all of this sql stuff against your db copy (making sure

you keep your sql in files so you can rerun against production)

Once you are happy you can do what you need to do quickly and correctly,

(iii) shutdown the dhis2 instance

(iv) make a backup of database (again)

(v) run the sql

(vi) restart dhis2 instance

Stay safe.

On 25 January 2018 at 13:05, moses mwale isoftcom.ic@gmail.com wrote:

This is live (Production) db created a backup. if something goes wrong will

revert. But the function itself doesnt affect anything right? what am going

to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering

jason.p.pickering@gmail.com wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this

procedure on your production system, unless you have thoroughly tested it in

a development environment!

Regards,

Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@gmail.com

wrote:

okay nice thanks, is it to load user-defined functions the same way you

load stored procedures, by packaging the Java class or classes into a JAR

file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering

jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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@gmail.com

wrote:

Hey Jason And Knut, thanks for the information, i have been trying to

understand the sql script shared and tried to execute but to no effect,

please can you help me understand where to place the source id and orgunit

ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be

merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Thanks very much, allow me to go through and implement the given

knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering

jason.p.pickering@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” knutst@gmail.com wrote:

Hi Moses,

I would take a backup of the database and then do this with an sql

script, just changing the sourceid (the database internal referent to the

organisationunitid) in the datavalues table. But you may get blocked if the

same period has been filled for both, in which case you may want to do a

“NOT IN” or left join.

Knut

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data,

some admin accidentally created other duplicates units into the system.

anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems

Knut Staring

Department of Information, Evidence and Research

World Health Organization, Geneva, Switzerland

Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522

Skype: knutstar

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

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 isoftcom.ic@gmail.com 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@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

developer_lusaka_systems

On Sun, Jan 28, 2018 at 5:28 PM, moses mwale isoftcom.ic@gmail.com wrote:

Hello Jason, Knut and Bob

I did according to your per instruction, i had a dump or copy of the db, i created a test db with it and loaded the functions then called using the shared query

dhis2_test=> SELECT merge_organisationunits(37442, 24872 ,SUM);
ERROR: column “sum” does not exist
LINE 1: SELECT merge_organisationunits(37442, 24872 ,SUM);
^

That’s the error i receive, what is wrong?

thanks in advance.


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Thu, Jan 25, 2018 at 3:32 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks all you are really help i appreciate so much. let me do test first on the backed up db if satisfied will run the merge on the Prod. Thanks.

developer_lusaka_systems

On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

If you really must work on your production database directly then at

the very least you should shutdown the dhis2 instance while you make

changes. Naturally you want to keep that down time to a minimum.

So I would suggest a sequence of:

(i) make a copy of the database to test these procedures

(ii) test out all of this sql stuff against your db copy (making sure

you keep your sql in files so you can rerun against production)

Once you are happy you can do what you need to do quickly and correctly,

(iii) shutdown the dhis2 instance

(iv) make a backup of database (again)

(v) run the sql

(vi) restart dhis2 instance

Stay safe.

On 25 January 2018 at 13:05, moses mwale isoftcom.ic@gmail.com wrote:

This is live (Production) db created a backup. if something goes wrong will

revert. But the function itself doesnt affect anything right? what am going

to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering

jason.p.pickering@gmail.com wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this

procedure on your production system, unless you have thoroughly tested it in

a development environment!

Regards,

Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@gmail.com

wrote:

okay nice thanks, is it to load user-defined functions the same way you

load stored procedures, by packaging the Java class or classes into a JAR

file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering

jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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@gmail.com

wrote:

Hey Jason And Knut, thanks for the information, i have been trying to

understand the sql script shared and tried to execute but to no effect,

please can you help me understand where to place the source id and orgunit

ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be

merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Thanks very much, allow me to go through and implement the given

knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering

jason.p.pickering@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” knutst@gmail.com wrote:

Hi Moses,

I would take a backup of the database and then do this with an sql

script, just changing the sourceid (the database internal referent to the

organisationunitid) in the datavalues table. But you may get blocked if the

same period has been filled for both, in which case you may want to do a

“NOT IN” or left join.

Knut

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data,

some admin accidentally created other duplicates units into the system.

anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems

Knut Staring

Department of Information, Evidence and Research

World Health Organization, Geneva, Switzerland

Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522

Skype: knutstar

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Alright thanks Jason on it.

···

On Mon, Jan 29, 2018 at 1:10 PM, Jason Pickering jason.p.pickering@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 isoftcom.ic@gmail.com 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?


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Jan 29, 2018 at 10:01 AM, Jason Pickering jason.p.pickering@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

developer_lusaka_systems

On Sun, Jan 28, 2018 at 5:28 PM, moses mwale isoftcom.ic@gmail.com wrote:

Hello Jason, Knut and Bob

I did according to your per instruction, i had a dump or copy of the db, i created a test db with it and loaded the functions then called using the shared query

dhis2_test=> SELECT merge_organisationunits(37442, 24872 ,SUM);
ERROR: column “sum” does not exist
LINE 1: SELECT merge_organisationunits(37442, 24872 ,SUM);
^

That’s the error i receive, what is wrong?

thanks in advance.


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Thu, Jan 25, 2018 at 3:32 PM, moses mwale isoftcom.ic@gmail.com wrote:

Thanks all you are really help i appreciate so much. let me do test first on the backed up db if satisfied will run the merge on the Prod. Thanks.

developer_lusaka_systems

On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

If you really must work on your production database directly then at

the very least you should shutdown the dhis2 instance while you make

changes. Naturally you want to keep that down time to a minimum.

So I would suggest a sequence of:

(i) make a copy of the database to test these procedures

(ii) test out all of this sql stuff against your db copy (making sure

you keep your sql in files so you can rerun against production)

Once you are happy you can do what you need to do quickly and correctly,

(iii) shutdown the dhis2 instance

(iv) make a backup of database (again)

(v) run the sql

(vi) restart dhis2 instance

Stay safe.

On 25 January 2018 at 13:05, moses mwale isoftcom.ic@gmail.com wrote:

This is live (Production) db created a backup. if something goes wrong will

revert. But the function itself doesnt affect anything right? what am going

to write now will i guess by merging duplicate orgs right?

On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering

jason.p.pickering@gmail.com wrote:

That looks correct.

It is of course worth saying, that you should not attempt to perform this

procedure on your production system, unless you have thoroughly tested it in

a development environment!

Regards,

Jason

On Thu, Jan 25, 2018 at 1:55 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Am using psql. see attached if its correct loading procedure.

On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring knutst@gmail.com wrote:

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale isoftcom.ic@gmail.com

wrote:

okay nice thanks, is it to load user-defined functions the same way you

load stored procedures, by packaging the Java class or classes into a JAR

file and then loading the JAR file using the LOAD CLASSES statement?

On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering

jason.p.pickering@gmail.com wrote:

Hi Moses,

You need to load both of those functions into your database.

  1. Script to delete an orgunit is here
  1. 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@gmail.com

wrote:

Hey Jason And Knut, thanks for the information, i have been trying to

understand the sql script shared and tried to execute but to no effect,

please can you help me understand where to place the source id and orgunit

ids in the script after several attempts of try its has became more complex.

I have attached a datavalue table and 1 duplicate orgunit to be

merged. please help me.

On Mon, Jan 8, 2018 at 8:26 PM, moses mwale isoftcom.ic@gmail.com

wrote:

Thanks very much, allow me to go through and implement the given

knowledge

On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering

jason.p.pickering@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” knutst@gmail.com wrote:

Hi Moses,

I would take a backup of the database and then do this with an sql

script, just changing the sourceid (the database internal referent to the

organisationunitid) in the datavalues table. But you may get blocked if the

same period has been filled for both, in which case you may want to do a

“NOT IN” or left join.

Knut

On Jan 7, 2018 10:29, “moses mwale” isoftcom.ic@gmail.com wrote:

Hey devs is it possible to merge facilities without losing data,

some admin accidentally created other duplicates units into the system.

anyone knows how its done?

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems

Knut Staring

Department of Information, Evidence and Research

World Health Organization, Geneva, Switzerland

Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522

Skype: knutstar

developer_lusaka_systems

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+46764147049

developer_lusaka_systems


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

developer_lusaka_systems

developer_lusaka_systems