Oracle Intersection of rows in a Table

Question

Hi I have a table with two columns (WID,DT) with data..

WID DT
-------
A   10
A   11
A   12
B   10
B   11
C   10
C   13

If I pass A,B in input i should get output as 10 and 11. Intersection of DT. If I pass A,B,C in input I should get 10 as output. If I pass A as input then 10,11,12 as output.. My input will be dynamic depends on some conditions from UI. How to achieve this in a query...

Answer 1

Here's a way that doesn't require you to add an intersect for each item in the list you pass in as the parameter. I can't guarantee that it will peform very well on large data sets, however, so you'd have to test with your data!

This first example shows you the results that mimics the effect of multiple values of the parameter being passed in at the same time (mainly to show you the results of the different test cases you mentioned):

with sample_data as (select 'A' WID, 10 DT from dual union all
                     select 'A' WID, 11 DT from dual union all
                     select 'A' WID, 12 DT from dual union all
                     select 'B' WID, 10 DT from dual union all
                     select 'B' WID, 11 DT from dual union all
                     select 'C' WID, 10 DT from dual union all
                     select 'C' WID, 13 DT from dual),
          params as (select 'A, B, C' val from dual union all
                     select 'A, B' val from dual union all
                     select 'A, C' val from dual union all
                     select 'B, C' val from dual union all
                     select 'A' val from dual union all
                     select 'B' val from dual union all
                     select 'C' val from dual),
    pivot_params as (select val,
                            trim(regexp_substr(val, '[^,]+', 1, level)) sub_val,
                            regexp_count(val||',', ',') num_vals
                     from   params
                     connect by prior val = val
                                and level <= regexp_count(val||',', ',')
                                and prior dbms_random.value is not null),
         results as (select sd.*,
                            pp.*,
                            count(distinct wid) over (partition by pp.val, sd.dt) cnt_of_distinct_wid_per_val
                     from   sample_data sd
                            inner join pivot_params pp on (sd.wid = pp.sub_val))
select distinct val param,
                dt
from   results
where  num_vals = cnt_of_distinct_wid_per_val
order by 1, 2;


PARAM           DT
------- ----------
A               10
A               11
A               12
A, B            10
A, B            11
A, B, C         10
A, C            10
B               10
B               11
B, C            10
C               10
C               13

This second example is much closer to what you'd need to pass in a parameter with a single value - you'd obviously not need the sample_data subquery (you'd just use your tablename in place of where sample_data is used in the main query), and you'd have to replace :param with the parameter name you're using in your code, but hopefully you can see what you'd need to amend to get it working in your code:

with sample_data as (select 'A' WID, 10 DT from dual union all
                     select 'A' WID, 11 DT from dual union all
                     select 'A' WID, 12 DT from dual union all
                     select 'B' WID, 10 DT from dual union all
                     select 'B' WID, 11 DT from dual union all
                     select 'C' WID, 10 DT from dual union all
                     select 'C' WID, 13 DT from dual),
     -- end of mimicking your data
     pivot_param as (select :param val,
                            trim(regexp_substr(:param, '[^,]+', 1, level)) sub_val,
                            regexp_count(:param||',', ',') num_vals
                     from   dual
                     connect by level <= regexp_count(:param||',', ',')),
         results as (select sd.*,
                            pp.*,
                            count(distinct wid) over (partition by pp.val, sd.dt) cnt_of_distinct_wid_per_val
                     from   sample_data sd
                            inner join pivot_param pp on (sd.wid = pp.sub_val))
select distinct val param,
                dt
from   results
where  num_vals = cnt_of_distinct_wid_per_val
order by 1, 2;

ETA: the way this works is: first, turn your list-as-a-parameter into a dummy table, with one row per item in the list, along with a count of how many items you passed in. (NB. I haven't taken into consideration the case where you have entered the same item twice in my query - you'd have to amend the way you identify the count of the items in the pivot_params subquery (probably using count(distinct(...) over (...)) and making sure the output was distinct.)

Once you have converted your parameter list into a table, you can then join it to the table containing your data (in my queries above, that would be the sample_data subquery), and find out how many unique WIDs are there per DT. If the count is the same as the count of items in your parameter list, then you know that all items are matched.

Answer 2

You can try like below using Oracle INTERSECT SET Operator

select DT from table1
where WID in ('A')
INTERSECT
select DT from table1
where WID in ('B');