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
Hi Harsh, you should give each uid a unique alias, eg uid1, uid2 etc
···
On 8 Aug 2016 08:21, “Harsh Atal” harsh.atal@gmail.com wrote:
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
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp