Filtering datasets and emulating the IN operator
Many times you want to filter a dataset where a columns value is equal to one of any item in a list. In SQL this is possible by utilizing the 'IN' operator. However, outside of using a QueryString parameter, due to the way that Named Query parameters work you can not supply a List of values.
If you create a custom property to hold the raw data set from the named query, then you can use a simple expression binding to filter the dataset further in a similar manner to that seen when using the SQL IN operator.
Filtering by a List of Values
unionAll(
columnsOf({path.to.raw.data}),
forEach(
{path.to.List},
where(
{path.to.raw.data},
it()['ColumnToFilter'] = it(1)
)
)
)
Note that for filtering by a single value, only the where() expression is needed.
Filtering by a Single Value
where(
{path.to.raw.data},
it()['ColumnToFilter'] = {path.to.filter.value}
)
Features employed
- Iterables General behavior of all iterators.
- unionAll() Assembles the list of filtered rows sources into a new filtered dataset.
- columnsOf() Returns an ordered map of the column names versus column type class names (as strings).
- forEach() Loops through the list of items, for comparison in the conditional argument of the
where()expression. - where() Prunes the source data according to supplied conditional expressions.
- it([depth]) Delivers the current iterations value based on the depth argument supplied.
Note that for static lists, the asList() expression can be used in place of {path.to.list}