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.picker...@gmail.com> 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.ntuny...@hotmail.com> 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.picker...@gmail.com >> To: moemedi.ntuny...@hotmail.com >> CC: dhis2-devs@lists.launchpad.net >> >> 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.ntuny...@hotmail.com> 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.picker...@gmail.com >> To: moemedi.ntuny...@hotmail.com >> CC: dhis2-devs@lists.launchpad.net >> >> >> 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.ntuny...@hotmail.com> 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@lists.launchpad.net >> Unsubscribe : https://launchpad.net/~dhis2-devs >> More help : https://help.launchpad.net/ListHelp >> >> >> >> >> -- >> Jason P. Pickering >> email: jason.p.picker...@gmail.com >> tel:+46764147049 >> >> >> >> >> -- >> Jason P. Pickering >> email: jason.p.picker...@gmail.com >> tel:+46764147049 >> > > > > -- > Jason P. Pickering > email: jason.p.picker...@gmail.com > tel:+46764147049 > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : dhis2-devs@lists.launchpad.net > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > >
_______________________________________________ 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
