Re: [Dhis2-users] SQL protected Table Issue
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 On Wed, Sep 12, 2018 at 2:00 PM tuzo engelbert wrote: > 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. , 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 >> > ___ > 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 > -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049 ___ 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
Re: [Dhis2-users] SQL protected Table Issue
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. , 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 > ___ 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
[Dhis2-users] SQL protected Table Issue
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
