OAC: How to Perform Expression Filtering with Attribute 'Like' Across a List of Values?

Question

Hello everyone,

I need to perform a filter expression in OAC DV like this: attribute LIKE '%' || (list of values) || '%'.

List of values comes from a parameter used as dashboard filter.

Looking at the logical query behind it, it seems that if I select multiple values in the dashboard filter, the query has been interpreted as follows:

* attribute LIKE '%' ||('value1', 'value2', ...)|| '%'

Obviously, it is semantically incorrect and it does not work. Is there a way to make system interpret the filter expression as:

* attribute LIKE '%'||'value1'||'%' OR attribute LIKE '%'||'value2'||'%' OR ...  ?

The number of selected values in the dashboard filter can change so i cannot write the query myself with a fixed number of OR operators.

Gianni Ceresa · Answer

https://community.oracle.com/products/oracleanalytics/discussion/comment/51544#Comment_51544 Did you click on "Convert this filter to SQL" in that popup? "Products"."P1 Product" LIKE concat(concat('%',@{your_variable}{'%'}),'%') Which obviously isn't going to work because it doesn't mean the same. I imagine it would work better with a static list of values because that can be turned into a proper SQL expression (the list of values is known before to even try to generate a query, but I didn't look at how that operator generates queries, I generally stay away from it). I suspect what Philippe has in mind by asking how many values (hoping an answer of 2-3 instead of 15-20) is that you can write a very long and bad looking string transformation set of operations on your variable to extract each component of the list of values and make it perform a LIKE '%value 1%' OR LIKE '%value 2%' etc. The thing is that you would need to write the logic for each element of the list and that's why the number of 15-20 means an ugly, bad performing, expression (because you have to write an expression handling 20 values.

Philippe Lions-Oracle · Answer

I can't think of a graceful workaround for this in current version. Maybe by creating multiple parameters and letting user set distinct values in each variable. That would allow you to sequence the filter syntax with correct 'OR' operators, and would be a robust solution. However that's a somewhat heavier functional experience for the user.

mzonelli · Answer

https://community.oracle.com/products/oracleanalytics/discussion/comment/51549#Comment_51549

Hello Philippe, i don't know the maximum number of values that a user may enter. But, if we can make it work or we can solve it just by limiting to 15/20 distinct values, I'm open to any suggestion.