dhis2-devs team mailing list archive

[Harsh Atal <harsh.atal@xxxxxxxxx>]

Dear Devs

I am trying to make a sql view for getting the uid and name of all the org
unit hierarchy . Have used following query : -

select
>
> ou1.uid,max(ou1.name),
> ou2.uid,max(ou2.name),
> ou3.uid,max(ou3.name),
> ou4.uid,max(ou4.name),
> ou5.uid,max(ou5.name),
> ou6.uid,max(ou6.name),
> ou7.uid,max(ou7.name),
> ou8.uid,max(ou8.name)
> from _orgunitstructure ous
> inner join organisationunit ou1 on ou1.organisationunitid = ous.idlevel1
> inner join organisationunit ou2 on ou2.organisationunitid = ous.idlevel2
> inner join organisationunit ou3 on ou3.organisationunitid = ous.idlevel3
> inner join organisationunit ou4 on ou4.organisationunitid = ous.idlevel4
> inner join organisationunit ou5 on ou5.organisationunitid = ous.idlevel5
> inner join organisationunit ou6 on ou6.organisationunitid = ous.idlevel6
> inner join organisationunit ou7 on ou7.organisationunitid = ous.idlevel7
> inner join organisationunit ou8 on ou8.organisationunitid = ous.idlevel8
> group by
> ou1.uid,
> ou2.uid,
> ou3.uid,
> ou4.uid,
> ou5.uid,
> ou6.uid,
> ou7.uid,
> ou8.uid
>
> This query runs fine in pgadmin but SQLview does not allow it to be saved
and shows - *ERROR: column "uid" specified more than once. *
PFA snapshot of the error message. DHIS2 version - 2.23.

Should this query not be accepted? Is this a bug? Any workarounds?

Regards
harsh

Attachment: SQL view error Screenshot from 2016-08-08 11:31:56.png
Description: PNG image