dhis2-devs team mailing list archive

["Arthur Gwatidzo" <arthur.g@xxxxxxxx>]

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:arthur.g@xxxxxxxx] 
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.pickering@xxxxxxxxx] 
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 <busoye@xxxxxxxx> 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.pickering@xxxxxxxxx> 
Date:16/02/2015 9:48 pm (GMT+00:00) 
To: Arthur Gwatidzo <arthur.g@xxxxxxxx> 
Cc: dhis2-devs <dhis2-devs@xxxxxxxxxxxxxxxxxxx> 
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.pickering@xxxxxxxxx> 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@xxxxxxxx> 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@xxxxxxxx

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@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~dhis2-devs
More help   : https://help.launchpad.net/ListHelp





 

-- 

Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049 <tel:%2B46764147049> 





 

-- 

Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049 <tel:%2B46764147049> 





 

-- 

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