SQL View : ERROR: column "uid" specified more than once.

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

Oh yes! Thank you Knut.

Regards

harsh

···

On 8 August 2016 at 12:07, Knut Staring knutst@gmail.com wrote:

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