Lea

Can I use a Multi-Select for many conditions of a single record?

Recommended Posts

Lea

I have a multi-select that I want to use as a filter for my query. One of the fields in my query has many values (codes) separated by a semi-colon (ATCO;EVAL;ASTR). I want to select a few values in the multi-select parameter and match ALL of the conditions selected (ATCO and ASTR as an example) to return a row, but if I choose more than 1 code it will not return data. Is there a way to query (with every like %) to return the data needed?

Share this post


Link to post
Michael Russo

So i'm not sure how practical this will be for your case, but I was able to accomplish this using 'connect by level' and 'regexp_like'.  Here's what it looks like:

 

select *
from
(
select t1.*, --whatever fields you are trying to get would be in place of this
       (
        select 'X'
        from
        (
         select regexp_substr(t1.vals,'[^;]+', 1, level) as val
         from dual
         connect by regexp_substr(t1.vals, '[^;]+', 1, level) is not null
        ) t2
        where t2.val = :ListBox1.Main --Change this to your multi-select list box
          and rownum = 1
      ) found
from
-- use your normal table here, i used this to test out that it works
(
 select 'test1' as nam,
        'ATCO;EVAL;ASTR' as vals
 from dual
 union
 select 'test2',
        'ATCA;EVO'
 from dual
) t1
)
where found = 'X'

You just replace the t1.vals with the field that contains your multiple values.  This could wind up being pretty slow depending on the amount of data you're working with. 

 

EDIT: Please note that this solution only works for oracle, just wanted to mention that.

Edited by Michael Russo
oracle disclaimer

Share this post


Link to post