Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End
Not surprising. The thing is, all of this should happen anyway at startup,but for some reason isn't. I think you will need to get a full log when the server starts up. If you are lucky,you will see some errors there. If not,you may need to get statement level logs from the postgresql server to see exactly why the table alteration scripts are not able to complete upon startup. Once you can get that information,maybe we can help further. Regards, Jason On Feb 17, 2015 4:50 PM, Arthur Gwatidzo arthu...@hisp.org wrote: Hi Jason Pickering, On using your two statements to resolve my issue: update sqlview set sqlviewid=viewid; alter table sqlview drop column viewid; For some reason on dropping the viewed column from the sqlview table , another error Error: ERROR: cannot drop table sqlview column viewid because other objects depend on it DETAIL: constraint fk39f92993428ceacc on table sqlviewusergroupaccesses depends on table sqlview column viewid HINT: Use DROP ... CASCADE to drop the dependent objects too. ** Error ** ERROR: cannot drop table sqlview column viewid because other objects depend on it SQL state: 2BP01 Detail: constraint fk39f92993428ceacc on table sqlviewusergroupaccesses depends on table sqlview column viewid Hint: Use DROP ... CASCADE to drop the dependent objects too. Regards, Arthur *From:* Arthur Gwatidzo [mailto:arthu...@hisp.org] *Sent:* 17 February 2015 08:02 AM *To:* 'Jason Pickering'; 'Adebusoye Anifalaje' *Cc:* 'dhis2-devs' *Subject:* RE: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Noted. Thanks Jason for helping. *From:* Jason Pickering [mailto:jason.p.picker...@gmail.com jason.p.picker...@gmail.com] *Sent:* 17 February 2015 12:36 AM *To:* Adebusoye Anifalaje *Cc:* Arthur Gwatidzo; dhis2-devs *Subject:* Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Actually not, have a nice early morning with a cup of coffee, but thanks for the concern, but maybe should have laid off that second cup as it seems I sent out another email to quickly. Lets try again I am just taking the statements from the TableAlteror.java, so you can use that for reference in case I get it wrong again update sqlview set sqlviewid=viweid; alter table sqlview drop column viewid; Best regards, Jason On Tue, Feb 17, 2015 at 6:13 AM, Adebusoye Anifalaje bus...@hisp.org wrote: Hi Jason you must be having a long night as you probably meant update sqlview set sqlviewid=viewid; Cheers Busoye Sent from my Samsung Galaxy smartphone. Original message From: Jason Pickering jason.p.picker...@gmail.com Date:16/02/2015 9:48 pm (GMT+00:00) To: Arthur Gwatidzo arthu...@hisp.org Cc: dhis2-devs dhis2-devs@lists.launchpad.net Subject: Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Sorry..send that one too fast Here was the SQL you need update sqlview set sqlviewid=viweid; update sqlview set sqlviewid=viweid; Best regards, Jason On Tue, Feb 17, 2015 at 5:47 AM, Jason Pickering jason.p.picker...@gmail.com wrote: Hi Arthur, Here is the problem. Caused by: org.postgresql.util.PSQLException: ERROR: null value in column viewid violates not-null constraint Detail: Failing row contains (null, RidpdaReportSQL, a0rnEhRIhG3, null, 2015-02-16 viewid has been renamed to sqlviewid but looks like your version of the database was either not upgraded or something else happened. If you have a sqlviewid column, it is probably safe to drop the viewid column. Otherwise, you should execute On Mon, Feb 16, 2015 at 10:17 PM, Arthur Gwatidzo arthu...@hisp.org wrote: Hi Devs, I am encountering an error when creating an SQL View using the front-end (Data Administration Utility of DHIS2). I tested this query in Postgre SQL and everything is okay. *Simple SQL View* SELECT de.name AS dataelementname,dv.value as Value,dv.comment, ou.uid As OrgUnitUID, ou.name,dv.periodid,pe.startdate,pe.enddate FROM datavalue dv INNER JOIN dataelement de ON(dv.dataelementid = de.dataelementid) AND ((de.uid='tATNxcPszv6' OR de.uid='zdShnywRyRP' OR de.uid='p33CeJ0tlE2' OR de.uid='ZEhUXlEjQoY' OR de.uid='Sgfn0Bw3OmZ' OR de.uid='MeHrnx6ypja' OR de.uid='EiHR3DtgJn9' OR de.uid='Xix3M7SHKCf' OR de.uid='Tlv3LImxRoW' OR de.uid='p6GJqwbCXqf')) INNER JOIN organisationunit ou ON( dv.sourceid = ou.organisationunitid) INNER JOIN period pe ON(dv.periodid = pe.periodid) ORDER BY OrgUnitUID; *Error in DHIS2 * *An exception occurred* Sorry! The system failed to execute the operation. Usually, no data is lost and you can continue working by going back to the previous page. I have attached a snapshot of the tomcat logs. What could be the problem Kind Regards, Arthur Gwatidzo Software Development and Information Systems
Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End
Actually not, have a nice early morning with a cup of coffee, but thanks for the concern, but maybe should have laid off that second cup as it seems I sent out another email to quickly. Lets try again I am just taking the statements from the TableAlteror.java, so you can use that for reference in case I get it wrong again update sqlview set sqlviewid=viweid; alter table sqlview drop column viewid; Best regards, Jason On Tue, Feb 17, 2015 at 6:13 AM, Adebusoye Anifalaje bus...@hisp.org wrote: Hi Jason you must be having a long night as you probably meant update sqlview set sqlviewid=viewid; Cheers Busoye Sent from my Samsung Galaxy smartphone. Original message From: Jason Pickering jason.p.picker...@gmail.com Date:16/02/2015 9:48 pm (GMT+00:00) To: Arthur Gwatidzo arthu...@hisp.org Cc: dhis2-devs dhis2-devs@lists.launchpad.net Subject: Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Sorry..send that one too fast Here was the SQL you need update sqlview set sqlviewid=viweid; update sqlview set sqlviewid=viweid; Best regards, Jason On Tue, Feb 17, 2015 at 5:47 AM, Jason Pickering jason.p.picker...@gmail.com wrote: Hi Arthur, Here is the problem. Caused by: org.postgresql.util.PSQLException: ERROR: null value in column viewid violates not-null constraint Detail: Failing row contains (null, RidpdaReportSQL, a0rnEhRIhG3, null, 2015-02-16 viewid has been renamed to sqlviewid but looks like your version of the database was either not upgraded or something else happened. If you have a sqlviewid column, it is probably safe to drop the viewid column. Otherwise, you should execute On Mon, Feb 16, 2015 at 10:17 PM, Arthur Gwatidzo arthu...@hisp.org wrote: Hi Devs, I am encountering an error when creating an SQL View using the front-end (Data Administration Utility of DHIS2). I tested this query in Postgre SQL and everything is okay. *Simple SQL View* SELECT de.name AS dataelementname,dv.value as Value,dv.comment, ou.uid As OrgUnitUID, ou.name,dv.periodid,pe.startdate,pe.enddate FROM datavalue dv INNER JOIN dataelement de ON(dv.dataelementid = de.dataelementid) AND ((de.uid='tATNxcPszv6' OR de.uid='zdShnywRyRP' OR de.uid='p33CeJ0tlE2' OR de.uid='ZEhUXlEjQoY' OR de.uid='Sgfn0Bw3OmZ' OR de.uid='MeHrnx6ypja' OR de.uid='EiHR3DtgJn9' OR de.uid='Xix3M7SHKCf' OR de.uid='Tlv3LImxRoW' OR de.uid='p6GJqwbCXqf')) INNER JOIN organisationunit ou ON( dv.sourceid = ou.organisationunitid) INNER JOIN period pe ON(dv.periodid = pe.periodid) ORDER BY OrgUnitUID; *Error in DHIS2 * *An exception occurred* Sorry! The system failed to execute the operation. Usually, no data is lost and you can continue working by going back to the previous page. I have attached a snapshot of the tomcat logs. What could be the problem Kind Regards, Arthur Gwatidzo [image: Description: cid:image001.png@01CCADE7.1D5397D0] Software Development and Information Systems *Health Information Systems Program* --- Email: arthu...@hisp.org Cell: +27(0)768983930 Skype: Arthur.Gwatidzo Web*: * http://www.hisp.org 66 Rigel Avenue North, Waterkloof Ridge Pretoria, 0181 South Africa E -25.7847787 S 28.2347984 This message may contain privileged and confidential information intended only for the person or entity to which it is addressed. Any review, retransmission, dissemination, copy or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient, is prohibited. If you received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the material from any computer. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of the entity transmitting the message. ___ 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
Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End
Hi Arthur, Here is the problem. Caused by: org.postgresql.util.PSQLException: ERROR: null value in column viewid violates not-null constraint Detail: Failing row contains (null, RidpdaReportSQL, a0rnEhRIhG3, null, 2015-02-16 viewid has been renamed to sqlviewid but looks like your version of the database was either not upgraded or something else happened. If you have a sqlviewid column, it is probably safe to drop the viewid column. Otherwise, you should execute On Mon, Feb 16, 2015 at 10:17 PM, Arthur Gwatidzo arthu...@hisp.org wrote: Hi Devs, I am encountering an error when creating an SQL View using the front-end (Data Administration Utility of DHIS2). I tested this query in Postgre SQL and everything is okay. *Simple SQL View* SELECT de.name AS dataelementname,dv.value as Value,dv.comment, ou.uid As OrgUnitUID, ou.name,dv.periodid,pe.startdate,pe.enddate FROM datavalue dv INNER JOIN dataelement de ON(dv.dataelementid = de.dataelementid) AND ((de.uid='tATNxcPszv6' OR de.uid='zdShnywRyRP' OR de.uid='p33CeJ0tlE2' OR de.uid='ZEhUXlEjQoY' OR de.uid='Sgfn0Bw3OmZ' OR de.uid='MeHrnx6ypja' OR de.uid='EiHR3DtgJn9' OR de.uid='Xix3M7SHKCf' OR de.uid='Tlv3LImxRoW' OR de.uid='p6GJqwbCXqf')) INNER JOIN organisationunit ou ON( dv.sourceid = ou.organisationunitid) INNER JOIN period pe ON(dv.periodid = pe.periodid) ORDER BY OrgUnitUID; *Error in DHIS2 * *An exception occurred* Sorry! The system failed to execute the operation. Usually, no data is lost and you can continue working by going back to the previous page. I have attached a snapshot of the tomcat logs. What could be the problem Kind Regards, Arthur Gwatidzo [image: Description: cid:image001.png@01CCADE7.1D5397D0] Software Development and Information Systems *Health Information Systems Program* --- Email: arthu...@hisp.org Cell: +27(0)768983930 Skype: Arthur.Gwatidzo Web*: * http://www.hisp.org 66 Rigel Avenue North, Waterkloof Ridge Pretoria, 0181 South Africa E -25.7847787 S 28.2347984 This message may contain privileged and confidential information intended only for the person or entity to which it is addressed. Any review, retransmission, dissemination, copy or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient, is prohibited. If you received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the material from any computer. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of the entity transmitting the message. ___ 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 ___ 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
Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End
Hi Jason Pickering, On using your two statements to resolve my issue: update sqlview set sqlviewid=viewid; alter table sqlview drop column viewid; For some reason on dropping the viewed column from the sqlview table , another error Error: ERROR: cannot drop table sqlview column viewid because other objects depend on it DETAIL: constraint fk39f92993428ceacc on table sqlviewusergroupaccesses depends on table sqlview column viewid HINT: Use DROP ... CASCADE to drop the dependent objects too. ** Error ** ERROR: cannot drop table sqlview column viewid because other objects depend on it SQL state: 2BP01 Detail: constraint fk39f92993428ceacc on table sqlviewusergroupaccesses depends on table sqlview column viewid Hint: Use DROP ... CASCADE to drop the dependent objects too. Regards, Arthur From: Arthur Gwatidzo [mailto:arthu...@hisp.org] Sent: 17 February 2015 08:02 AM To: 'Jason Pickering'; 'Adebusoye Anifalaje' Cc: 'dhis2-devs' Subject: RE: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Noted. Thanks Jason for helping. From: Jason Pickering [mailto:jason.p.picker...@gmail.com] Sent: 17 February 2015 12:36 AM To: Adebusoye Anifalaje Cc: Arthur Gwatidzo; dhis2-devs Subject: Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Actually not, have a nice early morning with a cup of coffee, but thanks for the concern, but maybe should have laid off that second cup as it seems I sent out another email to quickly. Lets try again I am just taking the statements from the TableAlteror.java, so you can use that for reference in case I get it wrong again update sqlview set sqlviewid=viweid; alter table sqlview drop column viewid; Best regards, Jason On Tue, Feb 17, 2015 at 6:13 AM, Adebusoye Anifalaje bus...@hisp.org wrote: Hi Jason you must be having a long night as you probably meant update sqlview set sqlviewid=viewid; Cheers Busoye Sent from my Samsung Galaxy smartphone. Original message From: Jason Pickering jason.p.picker...@gmail.com Date:16/02/2015 9:48 pm (GMT+00:00) To: Arthur Gwatidzo arthu...@hisp.org Cc: dhis2-devs dhis2-devs@lists.launchpad.net Subject: Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Sorry..send that one too fast Here was the SQL you need update sqlview set sqlviewid=viweid; update sqlview set sqlviewid=viweid; Best regards, Jason On Tue, Feb 17, 2015 at 5:47 AM, Jason Pickering jason.p.picker...@gmail.com wrote: Hi Arthur, Here is the problem. Caused by: org.postgresql.util.PSQLException: ERROR: null value in column viewid violates not-null constraint Detail: Failing row contains (null, RidpdaReportSQL, a0rnEhRIhG3, null, 2015-02-16 viewid has been renamed to sqlviewid but looks like your version of the database was either not upgraded or something else happened. If you have a sqlviewid column, it is probably safe to drop the viewid column. Otherwise, you should execute On Mon, Feb 16, 2015 at 10:17 PM, Arthur Gwatidzo arthu...@hisp.org wrote: Hi Devs, I am encountering an error when creating an SQL View using the front-end (Data Administration Utility of DHIS2). I tested this query in Postgre SQL and everything is okay. Simple SQL View SELECT de.name AS dataelementname,dv.value as Value,dv.comment, ou.uid As OrgUnitUID, ou.name,dv.periodid,pe.startdate,pe.enddate FROM datavalue dv INNER JOIN dataelement de ON(dv.dataelementid = de.dataelementid) AND ((de.uid='tATNxcPszv6' OR de.uid='zdShnywRyRP' OR de.uid='p33CeJ0tlE2' OR de.uid='ZEhUXlEjQoY' OR de.uid='Sgfn0Bw3OmZ' OR de.uid='MeHrnx6ypja' OR de.uid='EiHR3DtgJn9' OR de.uid='Xix3M7SHKCf' OR de.uid='Tlv3LImxRoW' OR de.uid='p6GJqwbCXqf')) INNER JOIN organisationunit ou ON( dv.sourceid = ou.organisationunitid) INNER JOIN period pe ON(dv.periodid = pe.periodid) ORDER BY OrgUnitUID; Error in DHIS2 An exception occurred Sorry! The system failed to execute the operation. Usually, no data is lost and you can continue working by going back to the previous page. I have attached a snapshot of the tomcat logs. What could be the problem Kind Regards, Arthur Gwatidzo Software Development and Information Systems Health Information Systems Program --- Email: arthu...@hisp.org Cell: +27(0)768983930 tel:%2B27%280%29768983930 Skype: Arthur.Gwatidzo Web: http://www.hisp.org/ http://www.hisp.org 66 Rigel Avenue North, Waterkloof Ridge Pretoria, 0181 South Africa E -25.7847787 S 28.2347984 This message may contain privileged and confidential information intended only for the person or entity to which it is addressed. Any review, retransmission, dissemination, copy or other use of, or taking of any action in reliance upon
[Dhis2-devs] Error Creating An SQL View through DHIS2 Front End
Hi Devs, I am encountering an error when creating an SQL View using the front-end (Data Administration Utility of DHIS2). I tested this query in Postgre SQL and everything is okay. Simple SQL View SELECT de.name AS dataelementname,dv.value as Value,dv.comment, ou.uid As OrgUnitUID, ou.name,dv.periodid,pe.startdate,pe.enddate FROM datavalue dv INNER JOIN dataelement de ON(dv.dataelementid = de.dataelementid) AND ((de.uid='tATNxcPszv6' OR de.uid='zdShnywRyRP' OR de.uid='p33CeJ0tlE2' OR de.uid='ZEhUXlEjQoY' OR de.uid='Sgfn0Bw3OmZ' OR de.uid='MeHrnx6ypja' OR de.uid='EiHR3DtgJn9' OR de.uid='Xix3M7SHKCf' OR de.uid='Tlv3LImxRoW' OR de.uid='p6GJqwbCXqf')) INNER JOIN organisationunit ou ON( dv.sourceid = ou.organisationunitid) INNER JOIN period pe ON(dv.periodid = pe.periodid) ORDER BY OrgUnitUID; Error in DHIS2 An exception occurred Sorry! The system failed to execute the operation. Usually, no data is lost and you can continue working by going back to the previous page. I have attached a snapshot of the tomcat logs. What could be the problem Kind Regards, Arthur Gwatidzo Description: cid:image001.png@01CCADE7.1D5397D0 Software Development and Information Systems Health Information Systems Program --- Email: arthu...@hisp.org Cell: +27(0)768983930 Skype: Arthur.Gwatidzo Web: http://www.hisp.org/ http://www.hisp.org 66 Rigel Avenue North, Waterkloof Ridge Pretoria, 0181 South Africa E -25.7847787 S 28.2347984 This message may contain privileged and confidential information intended only for the person or entity to which it is addressed. Any review, retransmission, dissemination, copy or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient, is prohibited. If you received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the material from any computer. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of the entity transmitting the message. * INFO 2015-02-16 15:51:39,896 'Arthur_Gwatidzo' create org.hisp.dhis.sqlview.SqlView, name: RidpdaReportSQL, uid: a0rnEhRIhG3 (AuditLogUtil.java [ht tp-apr-8080-exec-10]) * WARN 2015-02-16 15:51:39,901 SQL Error: 0, SQLState: 23502 (SqlExceptionHelper.java [http-apr-8080-exec-10]) * ERROR 2015-02-16 15:51:39,903 ERROR: null value in column viewid violates not-null constraint Detail: Failing row contains (null, RidpdaReportSQL, a0rnEhRIhG3, null, 2015-02-16 15:51:39.896, , SELECT de.name AS dataelementname,dv.value as Val ue,dv.comment, ..., 2177, 2015-02-16 15:51:39.896, 2163, rw--, f). (SqlExceptionHelper.java [http-apr-8080-exec-10]) * ERROR 2015-02-16 15:51:39,904 Error while executing action (ExceptionInterceptor.java [http-apr-8080-exec-10]) org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [viewid]; nested exception is org.hibernat e.exception.ConstraintViolationException: could not execute statement at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:163) at org.springframework.orm.hibernate4.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:730) at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:592) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:497) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy95.saveSqlView(Unknown Source) at org.hisp.dhis.dataadmin.action.sqlview.AddSqlViewAction.execute(AddSqlViewAction.java:103) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at
Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End
HiJason you must be having a long night as you probably meantupdate sqlview set sqlviewid=viewid;CheersBusoyeSent from my Samsung Galaxy smartphone. Original message From: Jason Pickering jason.p.picker...@gmail.com Date:16/02/2015 9:48 pm (GMT+00:00) To: Arthur Gwatidzo arthu...@hisp.org Cc: dhis2-devs dhis2-devs@lists.launchpad.net Subject: Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Sorry..send that one too fastHere was the SQL you needupdate sqlview set sqlviewid=viweid;update sqlview set sqlviewid=viweid;Best regards,JasonOn Tue, Feb 17, 2015 at 5:47 AM, Jason Pickering jason.p.picker...@gmail.com wrote:Hi Arthur,Here is the problem.Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "viewid" violates not-null constraint Detail: Failing row contains (null, RidpdaReportSQL, a0rnEhRIhG3, null, 2015-02-16 viewid has been renamed to "sqlviewid" but looks like your version of the database was either not upgraded or something else happened. If you have a "sqlviewid" column, it is probably safe to drop the "viewid" column. Otherwise, you should execute On Mon, Feb 16, 2015 at 10:17 PM, Arthur Gwatidzo arthu...@hisp.org wrote:Hi Devs,I am encountering an error when creating an SQL View using the front-end (Data Administration Utility of DHIS2).I tested this query in Postgre SQL and everything is okay.Simple SQL ViewSELECT de.name AS dataelementname,dv.value as Value,dv.comment, ou.uid As OrgUnitUID, ou.name,dv.periodid,pe.startdate,pe.enddateFROM datavalue dv INNER JOIN dataelement deON(dv.dataelementid = de.dataelementid) AND ((de.uid='tATNxcPszv6' OR de.uid='zdShnywRyRP' OR de.uid='p33CeJ0tlE2' OR de.uid='ZEhUXlEjQoY' OR de.uid='Sgfn0Bw3OmZ' OR de.uid='MeHrnx6ypja' OR de.uid='EiHR3DtgJn9' OR de.uid='Xix3M7SHKCf' OR de.uid='Tlv3LImxRoW' OR de.uid='p6GJqwbCXqf'))INNER JOIN organisationunit ouON( dv.sourceid = ou.organisationunitid) INNER JOIN period peON(dv.periodid = pe.periodid) ORDER BY OrgUnitUID;Error in DHIS2 An exception occurredSorry! The system failed to execute the operation. Usually, no data is lost and you can continue working by going back to the previous page.I have attached a snapshot of the tomcat logs.What could be the problemKind Regards,ArthurGwatidzoSoftware Development and Information Systems Health Information Systems Program---Email: arthu...@hisp.orgCell: +27(0)768983930 Skype: Arthur.GwatidzoWeb: http://www.hisp.org66 Rigel Avenue North, Waterkloof RidgePretoria, 0181South AfricaE -25.7847787 S 28.2347984This message may contain privileged and confidential information intended only for the person or entity to which it is addressed. Any review, retransmission, dissemination, copy or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient, is prohibited. If you received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the material from any computer. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of the entity transmitting the message.___ 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. Pickeringemail: jason.p.picker...@gmail.comtel:+46764147049 -- Jason P. Pickeringemail: jason.p.picker...@gmail.comtel:+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
Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End
Noted. Thanks Jason for helping. From: Jason Pickering [mailto:jason.p.picker...@gmail.com] Sent: 17 February 2015 12:36 AM To: Adebusoye Anifalaje Cc: Arthur Gwatidzo; dhis2-devs Subject: Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Actually not, have a nice early morning with a cup of coffee, but thanks for the concern, but maybe should have laid off that second cup as it seems I sent out another email to quickly. Lets try again I am just taking the statements from the TableAlteror.java, so you can use that for reference in case I get it wrong again update sqlview set sqlviewid=viweid; alter table sqlview drop column viewid; Best regards, Jason On Tue, Feb 17, 2015 at 6:13 AM, Adebusoye Anifalaje bus...@hisp.org wrote: Hi Jason you must be having a long night as you probably meant update sqlview set sqlviewid=viewid; Cheers Busoye Sent from my Samsung Galaxy smartphone. Original message From: Jason Pickering jason.p.picker...@gmail.com Date:16/02/2015 9:48 pm (GMT+00:00) To: Arthur Gwatidzo arthu...@hisp.org Cc: dhis2-devs dhis2-devs@lists.launchpad.net Subject: Re: [Dhis2-devs] Error Creating An SQL View through DHIS2 Front End Sorry..send that one too fast Here was the SQL you need update sqlview set sqlviewid=viweid; update sqlview set sqlviewid=viweid; Best regards, Jason On Tue, Feb 17, 2015 at 5:47 AM, Jason Pickering jason.p.picker...@gmail.com wrote: Hi Arthur, Here is the problem. Caused by: org.postgresql.util.PSQLException: ERROR: null value in column viewid violates not-null constraint Detail: Failing row contains (null, RidpdaReportSQL, a0rnEhRIhG3, null, 2015-02-16 viewid has been renamed to sqlviewid but looks like your version of the database was either not upgraded or something else happened. If you have a sqlviewid column, it is probably safe to drop the viewid column. Otherwise, you should execute On Mon, Feb 16, 2015 at 10:17 PM, Arthur Gwatidzo arthu...@hisp.org wrote: Hi Devs, I am encountering an error when creating an SQL View using the front-end (Data Administration Utility of DHIS2). I tested this query in Postgre SQL and everything is okay. Simple SQL View SELECT de.name AS dataelementname,dv.value as Value,dv.comment, ou.uid As OrgUnitUID, ou.name,dv.periodid,pe.startdate,pe.enddate FROM datavalue dv INNER JOIN dataelement de ON(dv.dataelementid = de.dataelementid) AND ((de.uid='tATNxcPszv6' OR de.uid='zdShnywRyRP' OR de.uid='p33CeJ0tlE2' OR de.uid='ZEhUXlEjQoY' OR de.uid='Sgfn0Bw3OmZ' OR de.uid='MeHrnx6ypja' OR de.uid='EiHR3DtgJn9' OR de.uid='Xix3M7SHKCf' OR de.uid='Tlv3LImxRoW' OR de.uid='p6GJqwbCXqf')) INNER JOIN organisationunit ou ON( dv.sourceid = ou.organisationunitid) INNER JOIN period pe ON(dv.periodid = pe.periodid) ORDER BY OrgUnitUID; Error in DHIS2 An exception occurred Sorry! The system failed to execute the operation. Usually, no data is lost and you can continue working by going back to the previous page. I have attached a snapshot of the tomcat logs. What could be the problem Kind Regards, Arthur Gwatidzo Software Development and Information Systems Health Information Systems Program --- Email: arthu...@hisp.org Cell: +27(0)768983930 tel:%2B27%280%29768983930 Skype: Arthur.Gwatidzo Web: http://www.hisp.org/ http://www.hisp.org 66 Rigel Avenue North, Waterkloof Ridge Pretoria, 0181 South Africa E -25.7847787 S 28.2347984 This message may contain privileged and confidential information intended only for the person or entity to which it is addressed. Any review, retransmission, dissemination, copy or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient, is prohibited. If you received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the material from any computer. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of the entity transmitting the message. ___ 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 tel:%2B46764147049 -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049 tel:%2B46764147049 -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049 ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs
