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=
--=20
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049=0A=
=0A=
--=20
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049=0A=
=
--_635ace38-bff5-4563-9d20-d1176e766647_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 12pt=3B
font-family:Calibri
}
--></style></head>
<body class=3D'hmmessage'><div dir=3D'ltr'>Thanks=2C will eliminate all of =
them. So far the query returned 5 records. But other records are referenced=
by foreign keys like:<br>chart_periods=3Breporttable_periods=3Bcompletedat=
asetregistration<br><br><font style=3D"" color=3D"#00B050" face=3D"Courier =
New"><b><i><font 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-siz=
e:8pt" size=3D"1">Co-founder KeyInConsulting</font></i></b></font><br><br><=
<div><hr id=3D"stopSpelling">Date: Mon=2C 15 Sep 2014 16:01:08 +0200<br>=
Subject: Re: [Dhis2-devs] periodstructure giving errors...<br>From: jason.p=
.pickering@gmail.com<br>To: moemedi.ntunyane@hotmail.com<br>CC: dhis2-devs@=
lists.launchpad.net<br><br><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. =3B<div><br></div><div>You may want to try something =
like</div><div><br></div><div> =3BSELECT startdate=2C periodtypeid=2CCO=
UNT(*) from period GROUP BY startdate=2Cperiodtypeid HAVING COUNT(*) >=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. =3B</=
<div><br></div><div>Regards=2C</div><div>Jason</div><div><br></div></di=
<div class=3D"ecxgmail_extra"><br><div class=3D"ecxgmail_quote">On Mon=2C=
Sep 15=2C 2014 at 3:53 PM=2C Moemedi Ntunyane <span dir=3D"ltr"><=3B<a h=
ref=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_blank">moemedi.ntuny=
ane@hotmail.com</a>>=3B</span> wrote:<br><blockquote class=3D"ecxgmail_qu=
ote" style=3D"border-left:1px #ccc 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 class=3D"h5"><br>Subject: Re: [Dhis2-devs] per=
iodstructure giving errors...<br>From: <a href=3D"mailto: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"_blank">moemedi.ntunyane=
@hotmail.com</a><br>CC: <a href=3D"mailto:dhis2-devs@lists.launchpad.net" t=
arget=3D"_blank">dhis2-devs@lists.launchpad.net</a><br><br><div dir=3D"ltr"=
So=2C the period =3B<span style=3D"font-family:arial=2Csans-serif=3Bfo=
nt-size:13px=3B">1589=3B6=3B"2010-02-01"=3B"2010-02-</span><span style=3D"f=
ont-family:arial=2Csans-serif=3Bfont-size:13px=3B">28 =2C points to a yearl=
y period type=2C which is not a year in duration. You are going to need to =
get rid of it. 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 C=
OUNT(*) FROM datavalue where periodid =3D =3B</span><span style=3D"font=
-family:arial=2Csans-serif=3Bfont-size:13px=3B">1589=3B =3B</span></div=
<div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><b=
</span></div><div><span style=3D"font-family:arial=2Csans-serif=3Bfont-si=
ze:13px=3B">If you have any records with this periodID=2C you are going to =
need to resolve these. This period looks to be monthly=2C so if the data is=
also monthly=2C you should be able to reassign it to a monthly period whic=
h starts in 2010-02-01 and ends in 2010-02-28=2C if it is already there. Yo=
u could try "SELECT * FROM period where startdate =3D '2010-02-01'::date=3B=
" and see if you get multiple periods for Feb 2010. If you have multiple pe=
riods for Feb 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></spa=
</div><div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=
=3B">UPDATE datavalue set periodid =3D ??? =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"=
where ??? is the periodid of the "real" Feb 2010 period. =3B</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">Then you should get rid of the bogus Feb 2010 period =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-ser=
if=3Bfont-size:13px=3B">with =3B</span></div><div><span style=3D"font-f=
amily:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><span s=
tyle=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">DELETE FROM per=
iod where periodid =3D =3B</span><span style=3D"font-family:arial=2Csan=
s-serif=3Bfont-size:13px=3B">1589=3B</span></div><div><span style=3D"font-f=
amily:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><font f=
ace=3D"arial=2C sans-serif">After that=2C you should clear your cache from =
Data administration. =3B</font></div><div><font face=3D"arial=2C sans-s=
erif"><br></font></div><div><font face=3D"arial=2C sans-serif">Do not try a=
ny of this on a production database!!!</font></div><div><font face=3D"arial=
=2C sans-serif"><br></font></div><div><font face=3D"arial=2C sans-serif">Re=
gards=2C</font></div><div><font face=3D"arial=2C sans-serif">Jason</font></=
<div><font face=3D"arial=2C sans-serif"><br></font></div></div><div><br=
<div>On Mon=2C Sep 15=2C 2014 at 12:14 PM=2C Moemedi Ntunyane <span dir=3D=
"ltr"><=3B<a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_blan=
k">moemedi.ntunyane@hotmail.com</a>>=3B</span> wrote:<br><blockquote styl=
e=3D"border-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. =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 =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> =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"><=
=3B<a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_blank">moemed=
i.ntunyane@hotmail.com</a>>=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  =3B<img src=3D"http:=3B//localhost:=
=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 =3B  =3B  =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 =3B  =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:+46764147049=
</div>=0A=
</div></div> </div></body>
</html>=
--_635ace38-bff5-4563-9d20-d1176e766647_--