0

I am learning Oracle SQL on the job, and am looking for some (ideally compact and efficient) conditional expression that evaluates to true if any item in list A occurs in list B.

I have a table IVS with a column of values CODES, and a table POP with a collection of columns (CDE_VAL_1 through CDE_VAL_26). For any row/record in POP, each of the CDE_VAL_* fields may contain a value from the IVS.CODES column, or else contain NULL. Say I have a subset of IVS (call it IVS_sub), then I am looking for a way select the subset of POP such that each selected record has some code from IVS_sub.CODES in any one of the CDE_VAL_* fields.

I have code that "works" but runs too slowly for me to test my code more than once or twice in a day. Obviously this is prohibitively slow, I need to be able to test and rewrite/fix the code with reasonable turnover.

This is my current (slow) method as a minimal example:

SELECT DISTINCT * FROM POP 
INNER JOIN IVS 
    ON IVS.CODES IN (CDE_VAL_1, /*... pretend i listed them all here*/, CDE_VAL_26)

Note that the actual query I'm running is much more complicated, has a couple other joins, filtering, etc (that being said, I am positive that my bottleneck is this issue, removing the IVS join takes the query from ~2.5 hours to ~10 minutes).

I have also tried:

SELECT DISTINCT * FROM POP 
INNER JOIN IVS ON (CDE_VAL_1 IN IVS.CODES
                   OR CDE_VAL_2 IN IVS.CODES
                   /* ... */
                   OR CDE_VAL_26 IN IVS.CODES)

However, this is cluttered and ugly, and more importantly it makes the code harder to follow. It also doesn't seem to run particularly faster, at least for small test queries I've run.

Preferably I'd like to end up with something like

SELECT * FROM POP
/* other joins and stuff */
INNER JOIN IVS ON ANY_OF(CDE_VAL_1, ... ) IN IVS.CODES
WHERE /*where conditions*/

or similar, where ANY_OF(...) IN ... is just standing in for whatever solution. Ideally that solution would be a relatively compact snippet with a clear meaning at a glance.

Conceptually this is just "If the intersection of these two lists isn't empty", but so far I haven't seen anything like that.

(Note: We aren't using PL/SQL, just pure Oracle SQL queries, though if there are solutions that require PL/SQL I'd be interested in hearing them, if only to use them as ammo to convince my boss to get us some training or something for it.)

Chrstfer CB
  • 53
  • 6
  • Did you check this question ? https://stackoverflow.com/questions/39001651/sql-check-one-list-against-another – programandoconro Oct 17 '19 at 19:24
  • For thought but not a recommended way. In the POP table with multiple columns of codes, select it as a long text string with vertical pipe delimiters between each CDE_VAL_X column. Then join against the other table where you select each code to find as "|code|" and join on text string contains. It avoids the if then else in a SQL query at the trade off of a string search. The cost/benefit depends on the number of rows, number of codes to match, etc. – stak Oct 17 '19 at 19:35
  • @RodrigoDíazLupanow I did see that one. Maybe I am not understanding the solutions put forward there, but I don't think it fully applies. The trouble is that where their version of ListA seems to be a type that can be selected from/joined with, in my case ListA would just be a list literal containing fields from any given row. Each row has its own codes that make up "ListA" for that row. – Chrstfer CB Oct 17 '19 at 20:47
  • @stak that's exactly what I thought when I first figured out this was a problem, but for as many codes, cols, and rows it ends up being so much slower than even my slow way that I didn't mention it above. – Chrstfer CB Oct 17 '19 at 20:47
  • 2
    Can you [edit] your question to include an [MRE] with DDL statements for a minimal example of our tables (you don't need all 26 columns) and DML statements for some example data and then provide your expected output for that data (and if necessary details of the logic to get from the input to the output). It may help us to understand what you are trying to achieve as the code on its own is rather confusing as to how you're expecting it to transform the data. – MT0 Oct 17 '19 at 23:48

1 Answers1

0

You can solve your problem by using unpivot. As an example:

  with ivs as (
    select '1' codes from  dual
     union all
    select '6' codes from  dual  ),
    pop as    (
        select  1 id, '1' CDE_VAL_1, '2' CDE_VAL_2 ,'3' CDE_VAL_3 , '4' CDE_VAL_4,'5' CDE_VAL_5 from  dual
    union all
         select 2 id, '7' CDE_VAL_1, '8' CDE_VAL_2 , '9' CDE_VAL_3 , '10' CDE_VAL_4, '6' CDE_VAL_5 from  dual
    ),
    pop_unpivot as (
    select * from pop
    unpivot
       ( 
            code_value  for code_name in (CDE_VAL_1, CDE_VAL_2, CDE_VAL_3, CDE_VAL_4, CDE_VAL_5)
        )
    ),
    match as (
    select distinct pop_unpivot.id,ivs.codes  
    from pop_unpivot,ivs where pop_unpivot.code_value=ivs.codes
    )
select pop.*,ivs.* from match,pop,ivs where match.id=pop.id and match.codes=ivs.codes;
thehazal
  • 81
  • 5