periodstructure giving errors...

jason · 15 September 2014 09:46

Very likely you have an invalid period in the periods table.=20
Can you run this and see what happens?
SELECT * FROM period where age(enddate=2Cstartdate) !=3D '1 year'::interval=
and periodtypeid =3D (SELECT periodtypeid from periodtype where name =3D '=
Yearly')=3B

Also=2C you should get an error which will help you to decipher which perio=
d is not correct =2C something like=20
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value vi=
olates unique constraint "in_periodstructure_iso" Detail: Key (iso)=3D(199=
5) already exists.
Best regards=2CJason

···

On Fri=2C Sep 12=2C 2014 at 5:03 PM=2C Moemedi Ntunyane <moemedi.ntunyane@h= otmail.com> wrote:
=0A=
=0A=
=0A=
Hi All

I have this problem while generating the resource tables...all other tables=
are fine except periodstructure...

Process=0A=
failed: PreparedStatementCallback=3B SQL [insert into _periodstructure =0A=
values (?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null =
value in column "iso" =0A=
violates not-null constraint=3B nested exception is =0A=
org.postgresql.util.PSQLException: ERROR: null value in column "iso" =0A=
violates not-null constraint =20

Regards=2C

Moemedi Ntunyane
Co-founder KeyInConsulting
=0A=

_______________________________________________
=0A=
Mailing list: https://launchpad.net/~dhis2-devs
=0A=
Post to : dhis2-devs@lists.launchpad.net
=0A=
Unsubscribe : https://launchpad.net/~dhis2-devs
=0A=
More help : https://help.launchpad.net/ListHelp
=0A=

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

_______________________________________________
=0A=
Mailing list: https://launchpad.net/~dhis2-devs
=0A=
Post to : dhis2-devs@lists.launchpad.net
=0A=
Unsubscribe : https://launchpad.net/~dhis2-devs
=0A=
More help : https://help.launchpad.net/ListHelp
=0A=

=

--_730ddc77-a82e-4516-a385-876ad4607153_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style></style></head>
<body class=3D'hmmessage'><div dir=3D'ltr'>I have eliminated all duplicates=
but this error still persist.<div><br></div><div>Process failed: PreparedS=
tatementCallback=3B SQL [insert into _periodstructure values (?=2C?=2C?=2C?=
=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null value in column "iso"=
violates not-null constraint=3B nested exception is org.postgresql.util.PS=
QLException: ERROR: null value in column "iso" violates not-null constraint=
.<br><br><font style=3D"" color=3D"#00B050" face=3D"Courier New"><b><i><fon=
t style=3D"font-size:8pt" size=3D"1">Moemedi Ntunyane</font><font style=3D"=
font-size:8pt" size=3D"1"><br></font><font style=3D"font-size:8pt" size=3D"=
1">Co-founder KeyInConsulting</font></i></b></font><br><br><br><div><hr id=
=3D"stopSpelling">From: moemedi.ntunyane@hotmail.com<br>To: larshelge@gmail=
.com=3B jason.p.pickering@gmail.com<br>CC: dhis2-devs@lists.launchpad.net<b=

Subject: RE: [Dhis2-devs] periodstructure giving errors...<br>Date: Mon=

=2C 15 Sep 2014 16:18:14 +0200<br><br>=0A=
=0A=
<style></style>=0A=
<div dir=3D"ltr">Hi Lars=2C<br><br>I have not done that. Im only capturing =
statistical data.<br><br>Regards=2C<br><br><font style=3D"" color=3D"#00B05=
0" face=3D"Courier New"><b><i><font style=3D"font-size:8pt=3B" size=3D"1">M=
oemedi Ntunyane</font><font style=3D"font-size:8pt=3B" size=3D"1"><br></fon=

<font style=3D"font-size:8pt=3B" size=3D"1">Co-founder KeyInConsulting</f=
</i></b></font><br><br><br><div><hr id=3D"ecxstopSpelling">Date: Mon=2C=

15 Sep 2014 16:10:13 +0200<br>Subject: Re: [Dhis2-devs] periodstructure gi=
ving errors...<br>From: larshelge@gmail.com<br>To: jason.p.pickering@gmail.=
com<br>CC: moemedi.ntunyane@hotmail.com=3B dhis2-devs@lists.launchpad.net<b=

<br><div dir=3D"ltr">Hi Moemedi=2C<div><br></div><div>may I ask=2C did yo=

u ever run the "aggregation queries" (producing aggregate values from event=
data) on your database?</div><div><br></div><div>regards=2C</div><div><br>=
</div><div>Lars</div><div><br></div></div><div class=3D"ecxgmail_extra"><br=

<div class=3D"ecxgmail_quote">On Mon=2C Sep 15=2C 2014 at 4:01 PM=2C Jason=

Pickering <span dir=3D"ltr">&lt=3B<a href=3D"mailto:jason.p.pickering@gmai=
l.com" target=3D"_blank">jason.p.pickering@gmail.com</a>&gt=3B</span> wrote=
:<br><blockquote class=3D"ecxgmail_quote" style=3D"border-left:1px #ccc sol=
id=3Bpadding-left:1ex=3B"><div dir=3D"ltr">I suspect you have more invalid =
periods then. You should follow the same procedure for other period types=
=2C i.e. checking to be sure that all months are one month=2C all weeks are=
one week=2C etc.&nbsp=3B<div><br></div><div>You may want to try something =
like</div><div><br></div><div>&nbsp=3BSELECT startdate=2C periodtypeid=2CCO=
UNT(*) from period GROUP BY startdate=2Cperiodtypeid HAVING COUNT(*) &gt=3B=
1 =3B<br></div><div><br></div><div>and see which periods and period types =
you have multiple records for. Ideally=2C this should not happen.&nbsp=3B</=

<div><br></div><div>Regards=2C</div><div>Jason</div><div><br></div></di=
<div class=3D"ecxHOEnZb"><div class=3D"h5"><div class=3D"ecxgmail_extra">=

<br><div class=3D"ecxgmail_quote">On Mon=2C Sep 15=2C 2014 at 3:53 PM=2C Mo=
emedi Ntunyane <span dir=3D"ltr">&lt=3B<a href=3D"mailto:moemedi.ntunyane@h=
otmail.com" target=3D"_blank">moemedi.ntunyane@hotmail.com</a>&gt=3B</span>=
wrote:<br><blockquote class=3D"ecxgmail_quote" style=3D"border-left:1px #c=
cc solid=3Bpadding-left:1ex=3B">=0A=
=0A=
=0A=
<div><div dir=3D"ltr">Hi Jason<br>Thnx=2C I had successfully elimated dupli=
cate records the error still pesist....it complaining of ISO column: is thi=
s column a date column??<br><br>Process=0A=
failed: PreparedStatementCallback=3B SQL [insert into _periodstructure =0A=
values (?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null =
value in column "iso" =0A=
violates not-null constraint=3B nested exception is =0A=
org.postgresql.util.PSQLException: ERROR: null value in column "iso" =0A=
violates not-null constraint.<span><br><br>Regards=2C<br><br><font color=3D=
"#00B050" face=3D"Courier New"><b><i><font style=3D"font-size:8pt=3B" size=
=3D"1">Moemedi Ntunyane</font><font style=3D"font-size:8pt=3B" size=3D"1"><=

</font><font style=3D"font-size:8pt=3B" size=3D"1">Co-founder KeyInConsu=

lting</font></i></b></font><br><br><br></span><div><hr>Date: Mon=2C 15 Sep =
2014 12:22:31 +0200<div><div><br>Subject: Re: [Dhis2-devs] periodstructure =
giving errors...<br>From: <a href=3D"mailto:jason.p.pickering@gmail.com" ta=
rget=3D"_blank">jason.p.pickering@gmail.com</a><br>To: <a href=3D"mailto:mo=
emedi.ntunyane@hotmail.com" target=3D"_blank">moemedi.ntunyane@hotmail.com<=
/a><br>CC: <a href=3D"mailto:dhis2-devs@lists.launchpad.net" target=3D"_bla=
nk">dhis2-devs@lists.launchpad.net</a><br><br><div dir=3D"ltr">So=2C the pe=
riod&nbsp=3B<span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=
=3B">1589=3B6=3B"2010-02-01"=3B"2010-02-</span><span style=3D"font-family:a=
rial=2Csans-serif=3Bfont-size:13px=3B">28 =2C points to a yearly period typ=
e=2C which is not a year in duration. You are going to need to get rid of i=
t. You will need to do something like</span><div><span style=3D"font-family=
:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><span style=
=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">SELECT COUNT(*) FRO=
M datavalue where periodid =3D&nbsp=3B</span><span style=3D"font-family:ari=
al=2Csans-serif=3Bfont-size:13px=3B">1589=3B&nbsp=3B</span></div><div><span=
style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></=

<div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"=
If you have any records with this periodID=2C you are going to need to res=

olve these. This period looks to be monthly=2C so if the data is also month=
ly=2C you should be able to reassign it to a monthly period which starts in=
2010-02-01 and ends in 2010-02-28=2C if it is already there. You could try=
"SELECT * FROM period where startdate =3D '2010-02-01'::date=3B" and see i=
f you get multiple periods for Feb 2010. If you have multiple periods for F=
eb 2010=2C then you need to do something like</span></div><div><span style=
=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><d=

<span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">UPDAT=

E datavalue set periodid =3D ???&nbsp=3B</span></div><div><span style=3D"=
font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><=
span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">where ???=
? is the periodid of the "real" Feb 2010 period.&nbsp=3B</span></div><div><=
span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></spa=

</div><div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=

=3B">Then you should get rid of the bogus Feb 2010 period&nbsp=3B</span></d=

<div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">=

<br></span></div><div><span style=3D"font-family:arial=2Csans-serif=3Bfont-=
size:13px=3B">with&nbsp=3B</span></div><div><span style=3D"font-family:aria=
l=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><span style=3D"fo=
nt-family:arial=2Csans-serif=3Bfont-size:13px=3B">DELETE FROM period where =
periodid =3D&nbsp=3B</span><span style=3D"font-family:arial=2Csans-serif=3B=
font-size:13px=3B">1589=3B</span></div><div><span style=3D"font-family:aria=
l=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><font face=3D"ari=
al=2C sans-serif">After that=2C you should clear your cache from Data admin=
istration.&nbsp=3B</font></div><div><font face=3D"arial=2C sans-serif"><br>=
</font></div><div><font face=3D"arial=2C sans-serif">Do not try any of this=
on a production database!!!</font></div><div><font face=3D"arial=2C sans-s=
erif"><br></font></div><div><font face=3D"arial=2C sans-serif">Regards=2C</=

</div><div><font face=3D"arial=2C sans-serif">Jason</font></div><div><=

font face=3D"arial=2C sans-serif"><br></font></div></div><div><br><div>On M=
on=2C Sep 15=2C 2014 at 12:14 PM=2C Moemedi Ntunyane <span dir=3D"ltr">&lt=
=3B<a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_blank">moemed=
i.ntunyane@hotmail.com</a>&gt=3B</span> wrote:<br><blockquote style=3D"bord=
er-left:1px #ccc solid=3Bpadding-left:1ex=3B">=0A=
=0A=
=0A=
<div><div dir=3D"ltr">Hi Jason<br><br>Thanx=2C I just run the query you gav=
e and no records were available and removed not =3D year(!=3Dyear) and reco=
rds where available. The date format is available as:<br><br>1573=3B6=3B"20=
10-01-01"=3B"2010-01-31"<br>1589=3B6=3B"2010-02-01"=3B"2010-02-28"<br><br>T=
he error available shows that there is key violation for null values for is=
o column in the periodstructure table.<span><font color=3D"#888888"><br><br=

<br><br><font color=3D"#00B050" face=3D"Courier New"><b><i><font style=3D"=

font-size:8pt=3B" size=3D"1">Moemedi Ntunyane</font><font style=3D"font-siz=
e:8pt=3B" size=3D"1"><br></font><font style=3D"font-size:8pt=3B" size=3D"1"=

Co-founder KeyInConsulting</font></i></b></font><br></font></span><div><br=
<br></div><div><div><hr>Date: Mon=2C 15 Sep 2014 11:46:07 +0200<br>Subject=

: Re: [Dhis2-devs] periodstructure giving errors...<br>From: <a href=3D"mai=
lto:jason.p.pickering@gmail.com" target=3D"_blank">jason.p.pickering@gmail.=
com</a><br>To: <a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_b=
lank">moemedi.ntunyane@hotmail.com</a><br>CC: <a href=3D"mailto:dhis2-devs@=
lists.launchpad.net" target=3D"_blank">dhis2-devs@lists.launchpad.net</a></=

<div><div><br><br><div dir=3D"ltr">Very likely you have an invalid peri=

od in the periods table.&nbsp=3B<div><br></div><div>Can you run this and se=
e what happens?</div><div><br></div><div>SELECT * FROM period where age(end=
date=2Cstartdate) !=3D '1 year'::interval and periodtypeid =3D (SELECT peri=
odtypeid from periodtype where name =3D 'Yearly')=3B<br></div><div><br></di=

<div>Also=2C you should get an error which will help you to decipher whic=

h period is not correct =2C something like&nbsp=3B</div><div><br></div><div=

<div>Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key va=

lue violates unique constraint "in_periodstructure_iso"</div><div>&nbsp=3B =
Detail: Key (iso)=3D(1995) already exists.</div></div><div><br></div><div>B=
est regards=2C</div><div>Jason</div><div><br></div></div><div><br><div>On F=
ri=2C Sep 12=2C 2014 at 5:03 PM=2C Moemedi Ntunyane <span dir=3D"ltr">&lt=
=3B<a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_blank">moemed=
i.ntunyane@hotmail.com</a>&gt=3B</span> wrote:<br><blockquote style=3D"bord=
er-left:1px #ccc solid=3Bpadding-left:1ex=3B">=0A=
=0A=
=0A=
<div><div dir=3D"ltr">Hi All<br><br>I have this problem while generating th=
e resource tables...all other tables are fine except periodstructure...<br>=
<br>Process=0A=
failed: PreparedStatementCallback=3B SQL [insert into _periodstructure =0A=
values (?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null =
value in column "iso" =0A=
violates not-null constraint=3B nested exception is =0A=
org.postgresql.util.PSQLException: ERROR: null value in column "iso" =0A=
violates not-null constraint &nbsp=3B<img src=3D"http&#58=3B//localhost&#58=
=3B8080/dhis2/images/error_small.png"><br><br>Regards=2C<br><br><font color=
=3D"#00B050" face=3D"Courier New"><b><i><font style=3D"font-size:8pt=3B" si=
ze=3D"1">Moemedi Ntunyane</font><font style=3D"font-size:8pt=3B" size=3D"1"=

<br></font><font style=3D"font-size:8pt=3B" size=3D"1">Co-founder KeyInCon=

sulting</font></i></b></font><br> </div></div>=0A=
<br>_______________________________________________<br>=0A=
Mailing list: <a href=3D"https://launchpad.net/~dhis2-devs" target=3D"_blan=
k">https://launchpad.net/~dhis2-devs</a><br>=0A=
Post to&nbsp=3B &nbsp=3B &nbsp=3B: <a href=3D"mailto:dhis2-devs@lists.launc=
hpad.net" target=3D"_blank">dhis2-devs@lists.launchpad.net</a><br>=0A=
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-devs" target=3D"_blan=
k">https://launchpad.net/~dhis2-devs</a><br>=0A=
More help&nbsp=3B &nbsp=3B: <a href=3D"https://help.launchpad.net/ListHelp"=
target=3D"_blank">https://help.launchpad.net/ListHelp</a><br>=0A=
<br></blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=
=3D"ltr">Jason P. Pickering<br>email: <a href=3D"mailto:jason.p.pickering@g=
mail.com" target=3D"_blank">jason.p.pickering@gmail.com</a><br>tel:<a targe=
t=3D"_blank">+46764147049</a></div>=0A=
</div></div></div></div> </div></div>=0A=
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=3D"=
ltr">Jason P. Pickering<br>email: <a href=3D"mailto:jason.p.pickering@gmail=
.com" target=3D"_blank">jason.p.pickering@gmail.com</a><br>tel:<a target=3D=
"_blank">+46764147049</a></div>=0A=
</div></div></div></div> </div></div>=0A=
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=3D"=
ltr">Jason P. Pickering<br>email: <a href=3D"mailto:jason.p.pickering@gmail=
.com" target=3D"_blank">jason.p.pickering@gmail.com</a><br>tel:<a target=3D=
"_blank">+46764147049</a></div>=0A=
</div>=0A=
</div></div><br>_______________________________________________<br>=0A=
Mailing list: <a href=3D"https://launchpad.net/~dhis2-devs" target=3D"_blan=
k">https://launchpad.net/~dhis2-devs</a><br>=0A=
Post to&nbsp=3B &nbsp=3B &nbsp=3B: <a href=3D"mailto:dhis2-devs@lists.launc=
hpad.net">dhis2-devs@lists.launchpad.net</a><br>=0A=
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-devs" target=3D"_blan=
k">https://launchpad.net/~dhis2-devs</a><br>=0A=
More help&nbsp=3B &nbsp=3B: <a href=3D"https://help.launchpad.net/ListHelp"=
target=3D"_blank">https://help.launchpad.net/ListHelp</a><br>=0A=
<br></blockquote></div><br></div></div> </div></div></div> =
</div></body>
</html>=

--_730ddc77-a82e-4516-a385-876ad4607153_--