Dear Jim, I have tested both scripts and they are all working. The second one is more straightforward for the analyzes that I will run with external list.
Thank you for your support! Regards, Tantely. Le 21 févr. 2017 9:32 PM, "Jim Grace" <j...@dhis2.org> a écrit : > If you want to show each pair of assigned orgUnit and dataSet, try > something like: > > SELECT ou.name AS orgunit, ds.name AS dataset > FROM dataset ds > JOIN datasetsource dss ON dss.datasetid = ds.datasetid > JOIN organisationunit ou ON ou.organisationunitid = dss.sourceid > ORDER BY ou.name, ds.name; > > If you want only one row per orgUnit and a column that concatenates all > assigned dataset names, try (in Postgresql): > > SELECT ou.name AS orgunit, string_agg(ds.name, ' | ' ORDER BY ds.name) AS > datasets > FROM dataset ds > JOIN datasetsource dss ON dss.datasetid = ds.datasetid > JOIN organisationunit ou ON ou.organisationunitid = dss.sourceid > GROUP BY ou.name > ORDER BY ou.name; > > Cheers, > Jim > > > On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely < > tramino...@mikolo.org> wrote: > >> Dear Community, >> >> I am trying to pull out the list of all orgUnits and the name of the >> questionnaire assigned to each of them via SQL View. In which table should >> I base the SQL query in order to have the list of dataSet assigned to an >> orgUnit? >> >> Regards, >> >> Tantely. >> >> *This message and its attachments are confidential and solely for the >> intended recipients. If received in error, please delete them and notify >> the sender via reply e-mail immediately.* >> >> _______________________________________________ >> 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 >> >> > > > -- > Jim Grace > Core developer, DHIS 2 > HISP US Inc. > http://www.dhis2.org <https://www.dhis2.org/> > -- *This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.*
_______________________________________________ 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