dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

Hi Johan,

You're right, there is a bug there. I can see I have fixed it locally, but
didn't update the attachment on the wiki, sorry.

Will upload and replace with my new version now.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo


On Fri, Apr 24, 2009 at 11:53 AM, <johansa@xxxxxxxxxx> wrote:

> Hi, I've downloaded and run the SQL creating views found here:
> https://answers.launchpad.net/dhis2/+faq/371
>
> I'm trying to understand the SQL here. Not an expert, but I don't see how
> the indicatorgroup is retrieved by the following code, for indicators OU2.
>
> I see that indicatorgroupmembers i mentioned in the FROM and WHERE
> statements, but not in the SELECT. What should I add to include the
> groups, which I want to pull through to the pivot tables? If somehow the
> indicatorgroups are retrieved by this view, what SQL would I need to add
> in the pivottable (using Pivot Play)
>
> Johan
>
>
> CREATE OR REPLACE VIEW pivotsource_indicator_ou2 AS
>
> SELECT indicator.name AS indicator, organisationunit_1.name AS orgunit1,
> organisationunit_1.shortname AS ou1, organisationunit_2.name AS orgunit2,
> organisationunit_2.shortname AS ou2, period.periodid, periodtype.name AS
> periodtype, to_char(period.startdate::timestamp with time zone,
> 'YYYY'::text) AS year, to_char(period.startdate::timestamp with time zone,
> 'Mon'::text) AS month, (rtrim(to_char(period.startdate::timestamp with
> time zone, 'Mon'::text)) || '-'::text) ||
> to_char(period.startdate::timestamp with time zone, 'YY'::text) AS period,
> aggregatedindicatorvalue.factor * aggregatedindicatorvalue.numeratorvalue
> AS numxfactor, aggregatedindicatorvalue.denominatorvalue,
> aggregatedindicatorvalue.annualized, aggregatedindicatorvalue.level
>
> FROM indicator indicator, indicatorgroupmembers indicatorgroupmembers,
> indicatorgroup indicatorgroup, organisationunit organisationunit_1,
> organisationunit organisationunit_2, period period, periodtype periodtype,
> aggregatedindicatorvalue aggregatedindicatorvalue, orgunitstructure
> orgunitstructure
>
> WHERE indicator.indicatorid = aggregatedindicatorvalue.indicatorid AND
> indicatorgroupmembers.indicatorid = indicator.indicatorid AND
> indicatorgroupmembers.indicatorgroupid = indicatorgroup.indicatorgroupid
> AND period.periodid = aggregatedindicatorvalue.periodid AND
> period.periodtypeid = periodtype.periodtypeid AND periodtype.name::text =
> 'Monthly'::text AND orgunitstructure.organisationunitid =
> aggregatedindicatorvalue.organisationunitid AND orgunitstructure.idlevel1
> = organisationunit_1.organisationunitid AND orgunitstructure.idlevel2 =
> organisationunit_2.organisationunitid AND aggregatedindicatorvalue.level =
> 2
>
> ORDER BY period.startdate;
>
> ALTER TABLE pivotsource_indicator_ou2 OWNER TO dhis;
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> More help   : https://help.launchpad.net/ListHelp
>