dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

SQL views in DHIS2 only allow SELECT statements, but one can define a
custom Procedural SQL function (Called through a SELECT statement),
which could perform an update statement. Otherwise, you can completely
go outside of DHIS2, and use something like PGAgent to perform these
custom tasks on a scheduled basis.

Again, as you can see from Ola and Lars's response, the "correct" way
would be to have the functionality directly in DHIS2, without having
to resort to custom SQL, which is of course potentially fraught with
its own set of problems, but could also be a potential interim
solution until the native DHIS2 functionality is in place.

Regards,
Jason


On Wed, May 11, 2011 at 11:19 AM, Friedman, Roger (CDC/CGH/DGHA) (CTR)
<rdf4@xxxxxxx> wrote:
> What I hear you saying is that we can get this in a report table but not in an indicator calculation or validation expression.
>
> Also, my recollection is that custom SQL view only allows select statements, so we can't use it to update a hidden data element, right?  And we could muck with the Hibernate definition to get a virtual column in the period table but we'd have no way to use it.
>
> -----Original Message-----
> From: Jason Pickering [mailto:jason.p.pickering@xxxxxxxxx]
> Sent: Wednesday, May 11, 2011 5:01 AM
> To: Friedman, Roger (CDC/CGH/DGHA) (CTR)
> Cc: dhis2-users@xxxxxxxxxxxxxxxxxxx
> Subject: Re: [Dhis2-users] Days per period
>
> It would be available through a pretty trivial SQL statement if you
> are calculating this on an external report.
>
> SELECT periodid, enddate-startdate as nbr_days FROM period in
> Postgresql will give you the number of days in a given period along
> with the periodid. you could then join this to the aggregated data
> elements you mention with periodid and the aggregateddatavalue table.
>
>  I think the easiest way would be through a custom SQL view (which is
> now supported) or through a report rather than direct calculation
> through the system.
>
> Regards,
> Jason
>
>
>
> On Wed, May 11, 2011 at 10:56 AM, Friedman, Roger (CDC/CGH/DGHA) (CTR)
> <rdf4@xxxxxxx> wrote:
>> Listers --
>>    We need to calculate bed occupancy rates.  We have the number of
>> beds per ward and the number of person-days of bed use per month, but we
>> need the number of days per month.  Is that available in some way from
>> the system or are we going to have to key it?
>> Thanks, Roger
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-users
>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+260974901293
>
>



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