Aggr. filter
Question

Hi ,

I want to filter data from above aggr. but my scenarios are 

Case1 : When I pass values is 1 then output should be 1st & 2nd row .
Case 2 : When I pass values is 2 then output should be 2st & 3rd row .
Case3 : When I pass values is 2,3 or 3,2  then output should be 2nd & 3rd row .

Case4 : When I pass values is 1,2 or 2,1 then output should be 1st , 2nd & 3rd row .

Guys, please help me , How to apply filter on aggr. for above use cases . Thanks in adv.

Regards,

NNG

aggr.PNG

Hi NNG,

the max number of items in the list is 2?

Regards

Hi @José Gonçalves ,

Max number could be varies its depend on data available like 1,2 or 2,3 or 3,4,5...etc

Being a dynamic list I think it is not possible to do it inside the aggregate filter.

The only solution is outside the aggregate with some logic including string_split and loops.

Thanks for your reply ,

 Data available in db is not limited , so string split function and looping take long time to display data on list.

If any other suggestion , appreciate your help .

I don't see any other solution except something like this:

Hi,

is search value is single digits or any limit it has ?

Hi Priya ,

Search value is only Integer string like 1,2,3 not like any decimal or text ex.1.0,2.5 ....etc

There is a way, using a function in the aggregate filter but will still need a string_split for the searched values inside that function.

Hi NNG,

the root cause of your problem is your datamodel. 

If your data would be stored as a one-to-many relation between 2 tables, let's call them test and testvalue, than you can use an sql widget with an in clause.

What you have there, the many side as a comma separated list in a single table column, that's just not what relational databases are intended for.

As things are now, you are stuck with solutions as offered by José

Dorine


Hi Dorine , 

I know its  relation between datamodel but this information already stored in that way , I need to find out solution for that , can't possible to change datamodel at this stage . Thanks!

I agree to Dorine,

please take into account that the current solution may give performance issues in the future. 

You might want to change your current wrapper 'TestCreateOrUpdate' that it will save each value redundantly to a new Entity 'TestValues' (Id, TestId, Value), so you can execute this query fast and clean. 

For the solution asked over here, it can be accomplished by:

 
select * from {Test}
Where ','+{Test}.[values]+',' like '%,'+@Input_Values+',%'
Where ','+Reverse({Test}.[values])+',' like '%,'+@Input_Values+',%'

Unless you want to search for e.g. 4,2,3 and than find 2,3,4.

Note that I start with , and end with , so that when you search for 1,2 you won't get 11,21 as result.  


Hi NNG,

It´ s never too late to fix a bad design ??

If you don´ t want to or are not allowed to touch the existing code and data model, you could, as Hans is suggesting, reduntantly store the split values.

But have you tried just applying the filter after an aggregate, as José is proposing?  How large is your table, are you really running into performance problems ?

If you worry about speed, you could even do something like splitting off the first 2 or 3 search values and use them as a like in the aggregate, and apply  any extra search values in a loop afterwards.

Dorine 

Hi NNG,

You can use advance query to do that, something like this:

For Oracle:

WITH splitvalues AS (

  select regexp_substr(@InputValue,'[^,]+', 1, level) as val from dual

  connect by regexp_substr(@InputValue, '[^,]+', 1, level) is not null)

 

SELECT distinct test.*

FROM   test

inner join splitvalues on test.val like concat('%',concat(splitvalues.val, '%'))

order by test.id


--

You can test here: http://sqlfiddle.com/#!4/fde5eb/15/0


//

For SQL Server, you can use this:

WITH splitvalues AS (

select value as val from STRING_SPLIT (@InputValue , ',' ) )



SELECT distinct test.*

FROM test

inner join splitvalues on test.val like concat('%',concat(splitvalues.val, '%'))

order by test.id


Example here: http://sqlfiddle.com/#!18/ca4fa/2

Hi Eduardo Pires,

Its look like what I want , let try to implement in OS , I have try on SQL fiddle , reply soon . Thank you !


Hi Eduardo,

STRING_SPLIT function not working in SQL widget of OS . Any other solution for this , to run sql query . Thank for you suggestion .

You can use index in the filter 

index( values , your_value ) > -1

Hi Eslam,

index function not works for case 3 and 4 . Thanks

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.