dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

Hi Moemedi,

may I ask, did you ever run the "aggregation queries" (producing aggregate
values from event data) on your database?

regards,

Lars


On Mon, Sep 15, 2014 at 4:01 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> I suspect you have more invalid periods then. You should follow the same
> procedure for other period types, i.e. checking to be sure that all months
> are one month, all weeks are one week, etc.
>
> You may want to try something like
>
>  SELECT startdate, periodtypeid,COUNT(*) from period GROUP BY
> startdate,periodtypeid HAVING COUNT(*) > 1 ;
>
> and see which periods and period types you have multiple records for.
> Ideally, this should not happen.
>
> Regards,
> Jason
>
>
> On Mon, Sep 15, 2014 at 3:53 PM, Moemedi Ntunyane <
> moemedi.ntunyane@xxxxxxxxxxx> wrote:
>
>> Hi Jason
>> Thnx, I had successfully elimated duplicate records the error still
>> pesist....it complaining of ISO column: is this column a date column??
>>
>> Process failed: PreparedStatementCallback; SQL [insert into
>> _periodstructure values (?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: null value in
>> column "iso" violates not-null constraint; nested exception is
>> org.postgresql.util.PSQLException: ERROR: null value in column "iso"
>> violates not-null constraint.
>>
>> Regards,
>>
>>
>> *Moemedi NtunyaneCo-founder KeyInConsulting*
>>
>>
>> ------------------------------
>> Date: Mon, 15 Sep 2014 12:22:31 +0200
>>
>> Subject: Re: [Dhis2-devs] periodstructure giving errors...
>> From: jason.p.pickering@xxxxxxxxx
>> To: moemedi.ntunyane@xxxxxxxxxxx
>> CC: dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>
>> So, the period 1589;6;"2010-02-01";"2010-02-28 , points to a yearly
>> period type, which is not a year in duration. You are going to need to get
>> rid of it. You will need to do something like
>>
>> SELECT COUNT(*) FROM datavalue where periodid = 1589;
>>
>> If you have any records with this periodID, you are going to need to
>> resolve these. This period looks to be monthly, so if the data is also
>> monthly, you should be able to reassign it to a monthly period which starts
>> in 2010-02-01 and ends in 2010-02-28, if it is already there. You could try
>> "SELECT * FROM period where startdate = '2010-02-01'::date;" and see if you
>> get multiple periods for Feb 2010. If you have multiple periods for Feb
>> 2010, then you need to do something like
>>
>> UPDATE datavalue set periodid = ?????
>>
>> where ???? is the periodid of the "real" Feb 2010 period.
>>
>> Then you should get rid of the bogus Feb 2010 period
>>
>> with
>>
>> DELETE FROM period where periodid = 1589;
>>
>> After that, you should clear your cache from Data administration.
>>
>> Do not try any of this on a production database!!!
>>
>> Regards,
>> Jason
>>
>>
>> On Mon, Sep 15, 2014 at 12:14 PM, Moemedi Ntunyane <
>> moemedi.ntunyane@xxxxxxxxxxx> wrote:
>>
>> Hi Jason
>>
>> Thanx, I just run the query you gave and no records were available and
>> removed not = year(!=year) and records where available. The date format is
>> available as:
>>
>> 1573;6;"2010-01-01";"2010-01-31"
>> 1589;6;"2010-02-01";"2010-02-28"
>>
>> The error available shows that there is key violation for null values for
>> iso column in the periodstructure table.
>>
>>
>>
>>
>> *Moemedi NtunyaneCo-founder KeyInConsulting*
>>
>>
>> ------------------------------
>> Date: Mon, 15 Sep 2014 11:46:07 +0200
>> Subject: Re: [Dhis2-devs] periodstructure giving errors...
>> From: jason.p.pickering@xxxxxxxxx
>> To: moemedi.ntunyane@xxxxxxxxxxx
>> CC: dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>
>>
>> Very likely you have an invalid period in the periods table.
>>
>> Can you run this and see what happens?
>>
>> SELECT * FROM period where age(enddate,startdate) != '1 year'::interval
>> and periodtypeid = (SELECT periodtypeid from periodtype where name =
>> 'Yearly');
>>
>> Also, you should get an error which will help you to decipher which
>> period is not correct , something like
>>
>> Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value
>> violates unique constraint "in_periodstructure_iso"
>>   Detail: Key (iso)=(1995) already exists.
>>
>> Best regards,
>> Jason
>>
>>
>> On Fri, Sep 12, 2014 at 5:03 PM, Moemedi Ntunyane <
>> moemedi.ntunyane@xxxxxxxxxxx> wrote:
>>
>> Hi All
>>
>> I have this problem while generating the resource tables...all other
>> tables are fine except periodstructure...
>>
>> Process failed: PreparedStatementCallback; SQL [insert into
>> _periodstructure values (?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: null value in
>> column "iso" violates not-null constraint; nested exception is
>> org.postgresql.util.PSQLException: ERROR: null value in column "iso"
>> violates not-null constraint
>>
>> Regards,
>>
>>
>> *Moemedi NtunyaneCo-founder KeyInConsulting*
>>
>> _______________________________________________
>> 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:+46764147049
>>
>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049
>>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>
> _______________________________________________
> 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
>
>