dhis2-devs team mailing list archive

[Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>]

OK, here is the re-construction of the statement query instance which is
modified for who wanna execute it following DHIS2's contruction in
PostgresQL.

*
**SELECT OrgUnit.Name AS OrgUnit, OrgUnitGroupSet.Name AS GroupSet,
Count(OrgUnitGroupSet.OrgUnitGroupSetID) AS "Group Set Allocations"
FROM OrganisationUnit AS
           OrgUnit INNER JOIN (
           OrgUnitGroupSet INNER JOIN (
           OrgUnitGroupSetMembers INNER JOIN
           OrgUnitGroupMembers
           ON OrgUnitGroupSetMembers.OrgUnitGroupID =
OrgUnitGroupMembers.OrgUnitGroupID)
           ON OrgUnitGroupSet.OrgUnitGroupSetID =
OrgUnitGroupSetMembers.OrgUnitGroupSetID)
           ON OrgUnit.OrganisationUnitID =
OrgUnitGroupMembers.OrganisationUnitID
WHERE OrgUnitGroupSet.Exclusive = true
GROUP BY OrgUnit.Name, OrgUnitGroupSet.Name
HAVING Count(OrgUnitGroupSet.OrgUnitGroupSetID) > 1
ORDER BY OrgUnit.Name;

*
On Mon, Mar 29, 2010 at 2:44 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Hi again,
>
> If you want, you can download a copy of DHIS 1.4 from hispkerala.org and
> take  a look at the DataIntegrityCheck table. For instance, the rule
> "OrgUnit Group Set Exclusive Violation"
>
> is defined as
>
> SELECT OrgUnit.OrgUnitName AS OrgUnit, OrgUnitGroupSet.OrgUnitGroupSetName
> AS GroupSet, Count(OrgUnitGroupSet.OrgUnitGroupSetID) AS [Group Set
> Allocations]
> FROM OrgUnit INNER JOIN (OrgUnitGroupSet INNER JOIN (OrgUnitGroupSetMember
> INNER JOIN OrgUnitGroupMember ON OrgUnitGroupSetMember.OrgUnitGroupID =
> OrgUnitGroupMember.OrgUnitGroupID) ON OrgUnitGroupSet.OrgUnitGroupSetID =
> OrgUnitGroupSetMember.OrgUnitGroupSetID) ON OrgUnit.OrgUnitID =
> OrgUnitGroupMember.OrgUnitID
> WHERE OrgUnitGroupSet.OrgUnitGroupSetExclusive=1 AND
> OrgUnitGroupMember.Active = 1
> GROUP BY OrgUnit.OrgUnitName, OrgUnitGroupSet.OrgUnitGroupSetName
> HAVING Count(OrgUnitGroupSet.OrgUnitGroupSetID)>1
> ORDER BY OrgUnit.OrgUnitName;
>
> The data integrity check functionality in 1.4 is essentially exactly the
> same as DHIS2, execept it is possible to extend the data integrity checks by
> adding new SQL statements in the 1.4 database. I would really like this see
> this feature in 2.0, as right now, all of the rules have been "hard-coded"
> in Java, and are not extendible, except by modification of the source code
> (Lars correct me if I am wrong). I expect that we could do it the 1.4 way,
> by using ANSI compatible queries that would work across Postgres, MySQL and
> H2. Anyway, that is another blueprint I think.
>
> Regards,
> Jason
>
>
> --
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+260968395190
>



-- 
Hieu.HISPVietnam
Good Health !