dhis2-users team mailing list archive

["Wilson,Randy" <rwilson@xxxxxxx>]

HI Morten,

Yes in the table psi_executiondate is stored in the format ‘2013-06-19’

I also find that the orgunit selection doesn’t display data from children.  When I want all data for the country, I can’t select ‘Rwanda’ – it gives me nothing.  If I remove the orgunitid filter I see all the data in PgAdmin.  I’ve added the line in red because we can then use this in a pivot table for analysis.

I’ve been having trouble extracting the data from the Individual reports data export – I think there may be issues with some of the records (there are many blanks and partial records in the system- from the early testing days that I haven’t figured out how to delete without going into each facility and deleting blank records through the data entry module.  Maybe with the data I have now through this query, I should be able to identify the psi_uids with problems and delete them from the backend.

select p.uid as p_uid, ps.uid as ps_uid, psi.uid as psi_uid, psi.status as psi_status, ou.uid as ou_uid,
ou.name as facilitname, ou.code as fosaid, de.name as dataelementname,
psi.executiondate as psi_executiondate, psi.completeduser as psi_completeduser, pdv.value as pdv_value,
pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, de.uid as de_uid from program p
left join programstage ps on ps.programid=p.programid
left join programstageinstance psi on ps.programstageid=psi.programstageid
left join organisationunit ou on (psi.organisationunitid=ou.organisationunitid)
left join patientdatavalue pdv on psi.programstageinstanceid=pdv.programstageinstanceid
left join dataelement de on pdv.dataelementid=de.dataelementid
where p.programid in (6130)  and ps.programstageid in (6131)  order by psi_uid;

Randy

From: Morten Olav Hansen [mailto:mortenoh@xxxxxxxxx]
Sent: Thursday, November 21, 2013 10:25 AM
To: Wilson,Randy
Cc: dhis2-users@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Dhis2-users] Problem with export of Event data

Hi Randy,

I haven't seen that issue before. I will have a look at it, I think it might be related to patientdatavalue using date, and not timestamp without timezone (as is being used in other places).

--
Morten

On Thu, Nov 21, 2013 at 8:16 AM, Wilson,Randy <rwilson@xxxxxxx<mailto:rwilson@xxxxxxx>> wrote:
We have several forms for neonatal, child and maternal death audits that are using the Single Event without registration module.

When I try to Export the data from the Import-Export menu I get an error regardless of which data set.

It appears to be a problem with the date parameter in the SQL query.  Below is the relevant section of the log:

type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [select p.uid as p_uid, ps.uid as ps_uid, psi.uid as psi_uid, psi.status as psi_status, ou.uid as ou_uid, psi.executiondate as psi_executiondate, psi.completeduser as psi_completeduser, pdv.value as pdv_value, pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, de.uid as de_uid from program p left join programstage ps on ps.programid=p.programid left join programstageinstance psi on ps.programstageid=psi.programstageid left join organisationunit ou on (psi.organisationunitid=ou.organisationunitid) left join patientdatavalue pdv on psi.programstageinstanceid=pdv.programstageinstanceid left join dataelement de on pdv.dataelementid=de.dataelementid  where p.programid in (6130)  and ps.programstageid in (6131)  and ou.organisationunitid in (756)  and (psi.executiondate >= 'Tue Jan 01 00:00:00 CAT 2013' and psi.executiondate <= 'Thu Nov 21 00:00:00 CAT 2013')  order by psi_uid;]; ERROR: invalid input syntax for type date: "Tue Jan 01 00:00:00 CAT 2013"
  Position: 805; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "Tue Jan 01 00:00:00 CAT 2013"


Has this been reported yet?  I can’t get the query to run through PgAdmin either unless I remove the date parameters.

Randy Wilson


_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx<mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx>
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp