SQL protected Table Issue

Support - Assistance technique
1

Hello everyone,

I am having an issue creating a SQL view from the DHIS2 using this query.

SELECT DISTINCT public._orgunitstructure.uidlevel4 AS UID4, public._organisationunitgroupsetstructure.organisationunitname AS name4, public._orgunitstructure.uidlevel5 AS UID5, public.organisationunit.name AS orgUnitName, public.organisationunit.hierarchylevel AS orgUnitLevel, public.trackedentityattributevalue.value AS woman, public.trackedentityinstance.uid AS womanuid, public.trackedentityinstance.created AS womancreationdate, public.trackedentityinstance.lastupdated AS womanupdated

FROM public.trackedentityattribute

INNER JOIN public.trackedentityattributevalue ON (public.trackedentityattribute.trackedentityattributeid = public.trackedentityattributevalue.trackedentityattributeid)

INNER JOIN public.trackedentityinstance ON (public.trackedentityattributevalue.trackedentityinstanceid = public.trackedentityinstance.trackedentityinstanceid)

INNER JOIN public.organisationunit ON (public.trackedentityinstance.organisationunitid = public.organisationunit.organisationunitid)

LEFT JOIN public._orgunitstructure ON public._orgunitstructure.uidlevel5 = public.organisationunit.uid

LEFT JOIN public._organisationunitgroupsetstructure ON public._organisationunitgroupsetstructure.organisationunitid = public.organisationunit.parentid

WHERE public.trackedentityattribute.name = ‘Name of Member’

Please, any advice on how to go about this.

Thanks

John

1 Like
2

Dear john,
If you have access at the backend(server side) you might need to create view of that trackedentityattributevalue like

create view te_trackedentutyattributevalue as select * from trackedentityattributevalue;

And hence use view created in your sql

Thanks

¡¡¡

On Tue, 11 Sep 2018, 4:54 p.m. , john.bidemi82@gmail.com wrote:

Hello everyone,

I am having an issue creating a SQL view from the DHIS2 using this query.

SELECT DISTINCT public._orgunitstructure.uidlevel4 AS UID4, public._organisationunitgroupsetstructure.organisationunitname AS name4, public._orgunitstructure.uidlevel5 AS UID5, public.organisationunit.name AS orgUnitName, public.organisationunit.hierarchylevel AS orgUnitLevel, public.trackedentityattributevalue.value AS woman, public.trackedentityinstance.uid AS womanuid, public.trackedentityinstance.created AS womancreationdate, public.trackedentityinstance.lastupdated AS womanupdated

FROM public.trackedentityattribute

INNER JOIN public.trackedentityattributevalue ON (public.trackedentityattribute.trackedentityattributeid = public.trackedentityattributevalue.trackedentityattributeid)

INNER JOIN public.trackedentityinstance ON (public.trackedentityattributevalue.trackedentityinstanceid = public.trackedentityinstance.trackedentityinstanceid)

INNER JOIN public.organisationunit ON (public.trackedentityinstance.organisationunitid = public.organisationunit.organisationunitid)

LEFT JOIN public._orgunitstructure ON public._orgunitstructure.uidlevel5 = public.organisationunit.uid

LEFT JOIN public._organisationunitgroupsetstructure ON public._organisationunitgroupsetstructure.organisationunitid = public.organisationunit.parentid

WHERE public.trackedentityattribute.name = ‘Name of Member’

Please, any advice on how to go about this.

Thanks

John

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

1 Like
3

Hi John and Tuzo,

While that may work, there are problems with that approach since during upgrades, that table may be modified, and if there is a view referencing it, the upgrade may fail. So, its not a recommended approach.

These tables are “protected” because the API enforces various sharing and security restrictions and an SQL view could easily expose data which would otherwise be protected.

Best approach is to use a report table or a direct call to the analytics API to get what you need.

Best regards,

Jason

¡¡¡

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

1 Like
4

Hi John,
Where you able to find a solution to your problem? I seem to be having a similar issue.

2 Likes
5

Hi @Uaa,

Are you able to use the Analytics API to get the information you need as @jason had suggested?

Best,
James.

6

Hi @jomutsani,
No, I am not. Can you guide me as to how to go about it.

Regards,
U’aa

2 Likes
7

@Uaa- Kindly check it out on the developer documentation Here: Analytics and let me know how it goes.

Best,
James.

2 Likes