Support of "IN" clauses in SQL Views / Queries?

Hi,

We’re attempting to pull data from DHIS 2 via DHIS 2 SQL Views / Queries and want to be able to use “IN” clauses to filter the data. Is there a way to do this?

For example we want to create a SQL View or query to get tracked entity data values, but only for specific data elements. The number of data elements that will be used in a filter will vary.

The documentation here doesn’t mention anything:

http://dhis2.github.io/dhis2-docs/master/en/developer/html/ch01s30.html

Attempting to create a query with a variable like this doesn’t seem to work (it seems to not allow quoting and commas):

select * from foo where bar in (${bars});

Alternately, is it possible to add criteria to a sql view where the criteria are appended as "OR"s?

view: select * from foo;

/api/sqlViews/UuRVaRmfHwa/data.csv?criteria=bar:azikLil2Nl1&criteria=bar:A2Kgu7zMgJr

(select * from foo where bar= azikLil2Nl1 OR bar = A2Kgu7zMgJr;)

This one seems more complicated because we would want a mixture of "AND"s and "OR"s for our particular view.

Please let us know thoughts on how we can get this to work for our use case.

Thanks!

Lorill

Hi Lorill,

good question - unfortunately, at the moment no.

This feature would clearly be useful though.

I have been thinking for a while that we should create a new API version of the SQL view criteria/filtering that matches the syntax we have for the Web API object filtering:

http://dhis2.github.io/dhis2-docs/master/en/developer/html/ch01s08.html

That way you could make filters e.g. like this:

/api/sqlViews/UuRVaRmfHwa/data.csv?filter=foo:eq:A2Kgu7zMgJr&bar:in:[azikLil2Nl1,bzikLil2Nl1]

Perhaps we could reuse some of the components we have for the Web API filter here.

Morten - what do you think?

regards,

Lars

···

On Wed, May 4, 2016 at 12:57 AM, Lorill Crees lcrees@2paths.com wrote:

Hi,

We’re attempting to pull data from DHIS 2 via DHIS 2 SQL Views / Queries and want to be able to use “IN” clauses to filter the data. Is there a way to do this?

For example we want to create a SQL View or query to get tracked entity data values, but only for specific data elements. The number of data elements that will be used in a filter will vary.

The documentation here doesn’t mention anything:

http://dhis2.github.io/dhis2-docs/master/en/developer/html/ch01s30.html

Attempting to create a query with a variable like this doesn’t seem to work (it seems to not allow quoting and commas):

select * from foo where bar in (${bars});

Alternately, is it possible to add criteria to a sql view where the criteria are appended as "OR"s?

view: select * from foo;

/api/sqlViews/UuRVaRmfHwa/data.csv?criteria=bar:azikLil2Nl1&criteria=bar:A2Kgu7zMgJr

(select * from foo where bar= azikLil2Nl1 OR bar = A2Kgu7zMgJr;)

This one seems more complicated because we would want a mixture of "AND"s and "OR"s for our particular view.

Please let us know thoughts on how we can get this to work for our use case.

Thanks!

Lorill


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

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

@Lars: possibly, at least the parsing logic… that said, most of the object filter stuff does automatic translation of properties etc… so it is only allowed to work on exposed properties, which I think is not good enough for SQLViews…

···

On Wed, May 4, 2016 at 8:30 PM, Lars Helge Øverland lars@dhis2.org wrote:

Hi Lorill,

good question - unfortunately, at the moment no.

This feature would clearly be useful though.

I have been thinking for a while that we should create a new API version of the SQL view criteria/filtering that matches the syntax we have for the Web API object filtering:

http://dhis2.github.io/dhis2-docs/master/en/developer/html/ch01s08.html

That way you could make filters e.g. like this:

/api/sqlViews/UuRVaRmfHwa/data.csv?filter=foo:eq:A2Kgu7zMgJr&bar:in:[azikLil2Nl1,bzikLil2Nl1]

Perhaps we could reuse some of the components we have for the Web API filter here.

Morten - what do you think?

regards,

Lars

Morten Olav Hansen

Senior Engineer, DHIS 2

University of Oslo

http://www.dhis2.org

On Wed, May 4, 2016 at 12:57 AM, Lorill Crees lcrees@2paths.com wrote:

Hi,

We’re attempting to pull data from DHIS 2 via DHIS 2 SQL Views / Queries and want to be able to use “IN” clauses to filter the data. Is there a way to do this?

For example we want to create a SQL View or query to get tracked entity data values, but only for specific data elements. The number of data elements that will be used in a filter will vary.

The documentation here doesn’t mention anything:

http://dhis2.github.io/dhis2-docs/master/en/developer/html/ch01s30.html

Attempting to create a query with a variable like this doesn’t seem to work (it seems to not allow quoting and commas):

select * from foo where bar in (${bars});

Alternately, is it possible to add criteria to a sql view where the criteria are appended as "OR"s?

view: select * from foo;

/api/sqlViews/UuRVaRmfHwa/data.csv?criteria=bar:azikLil2Nl1&criteria=bar:A2Kgu7zMgJr

(select * from foo where bar= azikLil2Nl1 OR bar = A2Kgu7zMgJr;)

This one seems more complicated because we would want a mixture of "AND"s and "OR"s for our particular view.

Please let us know thoughts on how we can get this to work for our use case.

Thanks!

Lorill


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

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org