dhis2-devs team mailing list archive

[moses mwale <isoftcom.ic@xxxxxxxxx>]

wow some major progress i thought it was special sql. I now know its normal
sql.

dhis2_test=> SELECT merge_organisationunits('FfX6DGeVsJd', 'qqkYhasbAcf'
,'SUM');
ERROR:  column "attributeoptioncomboid" is of type integer but expression
is of type character varying
LINE 6: value,
        ^
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO datavalueaudit SELECT
nextval('hibernate_sequence'::regclass),
dataelementid,
periodid,
( SELECT organisationunitid from organisationunit where uid = 'qqkYhasbAcf'
) as organisationunitid,
categoryoptioncomboid,
value,
now()::timestamp without time zone,
'admin'::character varying(100) as modifiedby,
'MERGE_SOURCE'::character varying(255) as audittype,
attributeoptioncomboid
FROM datavalue where sourceid = ( SELECT organisationunitid
from organisationunit where uid = 'FfX6DGeVsJd' )
CONTEXT:  PL/pgSQL function
merge_organisationunits(character,character,character varying) line 178 at
EXECUTE

if i change in the function will it work?

On Mon, Jan 29, 2018 at 10:01 AM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Hi Moses,
>
> You need to use the UID of the organisation unit not their integer ID.
>
> You need to place string literals in single quotes.
>
> So, it should be something like
>
> SELECT merge_organisationunits('foo', 'bar' ,'SUM');
>
>
> Regards,
> Jason
>
>
> On Sun, Jan 28, 2018 at 5:28 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
> wrote:
>
>> Hello Jason, Knut and Bob
>>
>> I did according to your per instruction, i had a dump or copy of the db,
>> i created a test db with it and loaded the functions then called using the
>> shared query
>>
>> dhis2_test=> SELECT merge_organisationunits(37442, 24872 ,SUM);
>> ERROR:  column "sum" does not exist
>> LINE 1: SELECT merge_organisationunits(37442, 24872 ,SUM);
>>                                                      ^
>> That's the error i receive, what is wrong?
>>
>> thanks in advance.
>>
>> On Thu, Jan 25, 2018 at 3:32 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
>> wrote:
>>
>>> Thanks all you are really help i appreciate so much. let me do test
>>> first on the backed up db if satisfied will run the merge on the Prod.
>>> Thanks.
>>>
>>> On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>>> wrote:
>>>
>>>> If you really must work on your production database directly then at
>>>> the very least you should shutdown the dhis2 instance while you make
>>>> changes.  Naturally you want to keep that down time to a minimum.
>>>>
>>>> So I would suggest a sequence of:
>>>>
>>>> (i) make a copy of the database to test these procedures
>>>> (ii) test out all of this sql stuff against your db copy (making sure
>>>> you keep your sql in files so you can rerun against production)
>>>> Once you are happy you can do what you need to do quickly and correctly,
>>>> (iii) shutdown the dhis2 instance
>>>> (iv) make a backup of database (again)
>>>> (v) run the sql
>>>> (vi) restart dhis2 instance
>>>>
>>>> Stay safe.
>>>>
>>>> On 25 January 2018 at 13:05, moses mwale <isoftcom.ic@xxxxxxxxx> wrote:
>>>> > This is live (Production) db created a backup. if something goes
>>>> wrong will
>>>> > revert. But the function itself doesnt affect anything right? what am
>>>> going
>>>> > to write now will i guess by merging duplicate orgs right?
>>>> >
>>>> > On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering
>>>> > <jason.p.pickering@xxxxxxxxx> wrote:
>>>> >>
>>>> >> That looks correct.
>>>> >>
>>>> >> It is of course worth saying, that you should not attempt to perform
>>>> this
>>>> >> procedure on your production system, unless you have thoroughly
>>>> tested it in
>>>> >> a development environment!
>>>> >>
>>>> >> Regards,
>>>> >> Jason
>>>> >>
>>>> >>
>>>> >>
>>>> >> On Thu, Jan 25, 2018 at 1:55 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
>>>> >> wrote:
>>>> >>>
>>>> >>> Am using psql. see attached if its correct loading procedure.
>>>> >>>
>>>> >>> On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring <knutst@xxxxxxxxx>
>>>> wrote:
>>>> >>>>
>>>> >>>> Just run everything in your PgAdmin SQL interface.
>>>> >>>>
>>>> >>>> On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <
>>>> isoftcom.ic@xxxxxxxxx>
>>>> >>>> wrote:
>>>> >>>>>
>>>> >>>>> okay nice thanks, is it to load user-defined functions the same
>>>> way you
>>>> >>>>> load stored procedures, by packaging the Java class or classes
>>>> into a JAR
>>>> >>>>> file and then loading the JAR file using the LOAD CLASSES
>>>> statement?
>>>> >>>>>
>>>> >>>>> On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering
>>>> >>>>> <jason.p.pickering@xxxxxxxxx> wrote:
>>>> >>>>>>
>>>> >>>>>> Hi Moses,
>>>> >>>>>>
>>>> >>>>>> You need to load both of those functions into your database.
>>>> >>>>>>
>>>> >>>>>> 1) Script to delete an orgunit is here
>>>> >>>>>> 2) Script to merge two orgunits is here
>>>> >>>>>>
>>>> >>>>>> After that, just call the function with
>>>> >>>>>>
>>>> >>>>>> SELECT merge_organisationunits(source_uid,dest_uid ,strategy);
>>>> >>>>>>
>>>> >>>>>> where `source_uid` is the UID of the organisation unit you want
>>>> to get
>>>> >>>>>> rid of, `dest_uid` is the UID of the organisation unit you want
>>>> to keep and
>>>> >>>>>> move data to
>>>> >>>>>> and `strategy` is one of the following:
>>>> >>>>>>
>>>> >>>>>> SUM: Returns the sum of the values, when there is overlapping
>>>> data.
>>>> >>>>>> MAX: Returns the max of the values, when there is overlapping
>>>> data.
>>>> >>>>>> MIN: Returns the min of the values, when there is overlapping
>>>> data.
>>>> >>>>>> AVG: Returns the mean of the values, when there is overlapping
>>>> data.
>>>> >>>>>> LAST: Returns the last value entered, when there is overlapping
>>>> data.
>>>> >>>>>> FIRST: Returns the first vale entered, when there is overlapping
>>>> data.
>>>> >>>>>>
>>>> >>>>>> Note that this only applies to numeric data. For other data
>>>> (Boolean,
>>>> >>>>>> text, etc) which cannot be aggregated numerically, the last
>>>> value will
>>>> >>>>>> always be taken.
>>>> >>>>>>
>>>> >>>>>> The merge script will not handle situations where you have
>>>> overlapping
>>>> >>>>>> tracker/event data, so you would need to figure out how to
>>>> handle that
>>>> >>>>>> yourself!
>>>> >>>>>>
>>>> >>>>>> Hope that helps to clarify and good luck!
>>>> >>>>>>
>>>> >>>>>> Regards,
>>>> >>>>>> Jason Pickering
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>> On Thu, Jan 25, 2018 at 5:30 AM, moses mwale <
>>>> isoftcom.ic@xxxxxxxxx>
>>>> >>>>>> wrote:
>>>> >>>>>>>
>>>> >>>>>>> Hey Jason And Knut, thanks for the information, i have been
>>>> trying to
>>>> >>>>>>> understand the sql script shared and tried to execute but to no
>>>> effect,
>>>> >>>>>>> please can you help me understand where to place the source id
>>>> and orgunit
>>>> >>>>>>> ids in the script after several attempts of try its has became
>>>> more complex.
>>>> >>>>>>>
>>>> >>>>>>> I have attached a datavalue table and 1 duplicate orgunit to be
>>>> >>>>>>> merged. please help me.
>>>> >>>>>>>
>>>> >>>>>>> On Mon, Jan 8, 2018 at 8:26 PM, moses mwale <
>>>> isoftcom.ic@xxxxxxxxx>
>>>> >>>>>>> wrote:
>>>> >>>>>>>>
>>>> >>>>>>>> Thanks very much, allow me to go through and implement the
>>>> given
>>>> >>>>>>>> knowledge
>>>> >>>>>>>>
>>>> >>>>>>>> On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering
>>>> >>>>>>>> <jason.p.pickering@xxxxxxxxx> wrote:
>>>> >>>>>>>>>
>>>> >>>>>>>>> Hi Moses
>>>> >>>>>>>>> We have some SQL scripts for this here.
>>>> >>>>>>>>>
>>>> >>>>>>>>>
>>>> >>>>>>>>> https://github.com/dhis2/dhis2-utils/blob/master/resources/s
>>>> ql/merge_orgunits.sql
>>>> >>>>>>>>>
>>>> >>>>>>>>> This script will not handle tracker data but could probably be
>>>> >>>>>>>>> adapted fairly easily to do so.
>>>> >>>>>>>>>
>>>> >>>>>>>>> Regards,
>>>> >>>>>>>>> Jason
>>>> >>>>>>>>>
>>>> >>>>>>>>>
>>>> >>>>>>>>>
>>>> >>>>>>>>> On Jan 7, 2018 7:28 AM, "Knut Staring" <knutst@xxxxxxxxx>
>>>> wrote:
>>>> >>>>>>>>>>
>>>> >>>>>>>>>> Hi Moses,
>>>> >>>>>>>>>> I would take a backup of the database and then do this with
>>>> an sql
>>>> >>>>>>>>>> script, just changing the sourceid (the database internal
>>>> referent to the
>>>> >>>>>>>>>> organisationunitid) in the datavalues table. But you may get
>>>> blocked if the
>>>> >>>>>>>>>> same period has been filled for both, in which case you may
>>>> want to do a
>>>> >>>>>>>>>> "NOT IN" or left join.
>>>> >>>>>>>>>>
>>>> >>>>>>>>>> Knut
>>>> >>>>>>>>>>
>>>> >>>>>>>>>> On Jan 7, 2018 10:29, "moses mwale" <isoftcom.ic@xxxxxxxxx>
>>>> wrote:
>>>> >>>>>>>>>>>
>>>> >>>>>>>>>>> Hey devs is it possible to merge facilities without losing
>>>> data,
>>>> >>>>>>>>>>> some admin accidentally created other duplicates units into
>>>> the system.
>>>> >>>>>>>>>>> anyone knows how its done?
>>>> >>>>>>>>>>>
>>>> >>>>>>>>>>> developer_lusaka_systems
>>>> >>>>>>>>>>>
>>>> >>>>>>>>>>> _______________________________________________
>>>> >>>>>>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>>>> >>>>>>>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>> >>>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>>> >>>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>> >>>>>>>>>>>
>>>> >>>>>>>>>>
>>>> >>>>>>>>>> _______________________________________________
>>>> >>>>>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>>>> >>>>>>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>> >>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>>> >>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>> >>>>>>>>>>
>>>> >>>>>>>>
>>>> >>>>>>>>
>>>> >>>>>>>>
>>>> >>>>>>>> --
>>>> >>>>>>>> developer_lusaka_systems
>>>> >>>>>>>
>>>> >>>>>>>
>>>> >>>>>>>
>>>> >>>>>>>
>>>> >>>>>>> --
>>>> >>>>>>> developer_lusaka_systems
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>> --
>>>> >>>>>> Jason P. Pickering
>>>> >>>>>> email: jason.p.pickering@xxxxxxxxx
>>>> >>>>>> tel:+46764147049
>>>> >>>>>
>>>> >>>>>
>>>> >>>>>
>>>> >>>>>
>>>> >>>>> --
>>>> >>>>> developer_lusaka_systems
>>>> >>>>
>>>> >>>>
>>>> >>>>
>>>> >>>>
>>>> >>>> --
>>>> >>>> Knut Staring
>>>> >>>>
>>>> >>>> Department of Information, Evidence and Research
>>>> >>>> World Health Organization, Geneva, Switzerland
>>>> >>>> Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
>>>> >>>> Skype:     knutstar
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>> --
>>>> >>> developer_lusaka_systems
>>>> >>
>>>> >>
>>>> >>
>>>> >>
>>>> >> --
>>>> >> Jason P. Pickering
>>>> >> email: jason.p.pickering@xxxxxxxxx
>>>> >> tel:+46764147049
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > --
>>>> > developer_lusaka_systems
>>>> >
>>>> > _______________________________________________
>>>> > Mailing list: https://launchpad.net/~dhis2-devs
>>>> > Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>> > Unsubscribe : https://launchpad.net/~dhis2-devs
>>>> > More help   : https://help.launchpad.net/ListHelp
>>>> >
>>>>
>>>
>>>
>>>
>>> --
>>> developer_lusaka_systems
>>>
>>
>>
>>
>> --
>> developer_lusaka_systems
>>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049 <+46%2076%20414%2070%2049>
>



-- 
developer_lusaka_systems