dhis2-devs team mailing list archive

[Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>]

Yes, thank you Jason for modifying on this statement. I tried then it's
working fine.

P.S: To check this function's returned result just using this: SELECT * FROM
migrate_cdes()

2011/4/28 Jason Pickering <jason.p.pickering@xxxxxxxxx>

> This is a start maybe for postgres.
>
>
> CREATE OR REPLACE FUNCTION migrate_cdes() RETURNS INTEGER AS
> $BODY$
> DECLARE
>
> this_indicatortypeid integer;
> BEGIN
>
> SELECT INTO this_indicatortypeid indicatortypeid From IndicatorType Where
> Indicatorfactor = 1 LIMIT 1;
>
> EXECUTE 'INSERT INTO indicator (
> indicatorid, uuid, name, alternativename, shortname, code,
> description, annualized, indicatortypeid, numerator, numeratordescription,
> numeratoraggregationtype, denominator, denominatordescription,
> denominatoraggregationtype, sortorder, url, lastupdated )
> (
> select de.dataelementid, de.uuid, de.name, de.alternativename,
> de.shortname, de.code,
> de.description, false,'
> || this_indicatortypeid
> ||',e.expression, e.description,
> de.aggregationtype, null, null, de.aggregationtype, null, de.url,
> de.lastupdated
> from dataelement de, calculateddataelement cde, expression e
> where (de.dataelementid = cde.calculateddataelementid) and
> (cde.expressionid = e.expressionid));';
>
> RETURN 1;
>
>   END;
>   $BODY$
>   LANGUAGE 'plpgsql' VOLATILE COST 100
>
> 2011/4/27 Lars Helge Øverland <larshelge@xxxxxxxxx>
>
>>
>> I also believe that a SQL script would be appropriate for this and it
>> seems Hieu is conceptually right here..
>>
>> Lars
>>
>>
>> On Mon, Apr 25, 2011 at 9:19 AM, Jason Pickering <
>> jason.p.pickering@xxxxxxxxx> wrote:
>>
>>> Hi Hieu,
>>>
>>> Yes, this is along the same lines of  what I was thinking, but was not as
>>> quick as you about coming up with the SQL. :)
>>>
>>> I will try this query out (modified to fit Postgres) and see how it
>>> works.
>>>
>>>
>>> Regards,
>>> Jason
>>>
>>>
>>> On Mon, Apr 25, 2011 at 8:55 AM, Hieu Dang Duy <
>>> hieu.hispvietnam@xxxxxxxxx> wrote:
>>>
>>>> Hello Jason,
>>>>
>>>> In this case, I think we have to make a store procedure or something
>>>> like this statement to convert from CDEs to Indicator as:
>>>>
>>>> Declare @IndicatorTypeId;
>>>>
>>>> Set @IndicatorTypeId = (Select indicatortypeid From IndicatorType Where
>>>> Indicatorfactor = 1);
>>>>
>>>> INSERT INTO indicator (
>>>>             indicatorid, uuid, name, alternativename, shortname, code,
>>>>             description, annualized, indicatortypeid, numerator,
>>>> numeratordescription,
>>>>             numeratoraggregationtype, denominator,
>>>> denominatordescription,
>>>>             denominatoraggregationtype, sortorder, url, lastupdated )
>>>>     (
>>>>         select de.dataelementid, de.uuid, de.name, de.alternativename,
>>>> de.shortname, de.code,
>>>>             de.description, false, *@IndicatorTypeId*, e.expression,
>>>> e.description,
>>>>             de.aggregationtype, null, null, de.aggregationtype, null,
>>>> de.url, de.lastupdated
>>>>             from dataelement de, calculateddataelement cde, expression e
>>>>             where (de.dataelementid = cde.calculateddataelementid) and
>>>> (cde.expressionid = e.expressionid
>>>>   ));
>>>>
>>>> How to you think ?
>>>>
>>>> On Mon, Apr 25, 2011 at 11:20 AM, Jason Pickering <
>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>
>>>>>  Feeling like some spring cleaning on Easter, Lars?
>>>>>
>>>>> I am thinking if may be necessary to document how to convert CDEs to
>>>>> indicators. We have  a large number of them in , and recreation by hand will
>>>>> be very painful.
>>>>>
>>>>> It would seem that transferring of the "expression"
>>>>> from calculateddataelement to the "numerator" field of indicator would be
>>>>> feasible.
>>>>>
>>>>> The other thing I had thought before we hacked up the 1.4 code was to
>>>>> import 1.4 CDEs as indicators. This would seem to be feasible as well but
>>>>> would obviously require work.
>>>>>
>>>>> Any thoughts about how to convert CDEs to indicators, and if we are
>>>>> still going to maintain some level of backwards compatibility?
>>>>>
>>>>> Regards,
>>>>> Jason
>>>>>
>>>>>
>>>>>
>>>> --
>>>> Good health !
>>>>
>>>>
>>>
>>>
>>> --
>>> Jason P. Pickering
>>> email: jason.p.pickering@xxxxxxxxx
>>> tel:+260974901293
>>>
>>> _______________________________________________
>>> 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
>>>
>>>
>>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+260974901293
>



-- 
Good health !