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

TomcatLog.txt (20.1 KB)

···

Email: arthur.g@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.

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

image

···

On Mon, Feb 16, 2015 at 10:17 PM, Arthur Gwatidzo arthur.g@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: arthur.g@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.pickering@gmail.com
tel:+46764147049

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

image

···

On Tue, Feb 17, 2015 at 5:47 AM, Jason Pickering jason.p.pickering@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 arthur.g@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: arthur.g@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.pickering@gmail.com
tel:+46764147049

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049