Hello Oracle Masters,

I was looking at 'connect by level' ( https://asktom.oracle.com/pls/apex/asktom.search?tag=clever-sql ) hoping I could make use of it, but don't seem to be able to. Perhaps there is another way?

We have a table of products and a table of exclusions. 'Products' have a name
(of which only a substring form is usable as a 'code').
'Exclusions' also have the same code, but multiple codes are stored in the same column separated by slash.

For example:
Products:
name = 'ABC*1.2.3.XYZ' (substring will strip out the usable part for comparison)
name = 'KLM*5.A'

Exclusions:
code = 'ABC/KLM'
code = 'XYZ'
code = 'OPQ/RST/UVW/EFG'


Have a horrendously long and complex query that pulls up all the products except those being excluded (and a sister variant of only those excluded).
We only used to care about the first code in the list.
Summed up, query looks like so:

select <loads>
from <loads of tables, including <i>'products p'</i>
where <complex set of conditions>
and exists
     (select from exclusions e where <conditions>
      and ((e.code like '%/%' and subsr(e.code)  = substr(p.name))
            or
           (e.code = substr(p.name))
      )

Now the requirements have changed and we want the query to return records when the [substring] of product name matches ANY of the codes in the slash-separated list of exclusion codes...


Rewriting the whole query from scratch isn't really possible--the bit I provided above is just the inner-most wrapped alias; there are more levels of it being aliased, joined to something else, etc....
(It's also an 'exists' rather than part of the join because there is another flavor of it elsewhere in the code
that's 'not exists', and both should work the same).

That said, the subset of the preferences table with some basic where conditions (e.g., records for this year)
is about 10K records.


Some code to recreate the scenario:

create table products (key varchar2(5) not null, name varchar2(30) not null);
insert into products values ('AB47', 'AB345*Some name');
insert into products values ('AB47', 'AB345*Some name');
insert into products values ('AB88', 'AB345*Some name');
insert into products values ('AB47', 'AB345.A*Some name');
insert into products values ('AB22', 'AB999*Another name');

create table exclusions (code varchar2(30) not null);
insert into exclusions values ('AB345/AB999');
insert into exclusions values ('QD523');


select p.*
from products p
where p.key = 'AB47' -- AB22
and exists (select null from exclusions e where ((e.code like '%/%' and 
              substr(e.code,1,instr(e.code,'/')-1) = 
                                     substr(p.name,1,instr(p.name,'*')-1))
            or
           (e.code = substr(p.name,1,instr(p.name,'*')-1)))
      )
;

Right now key AB47 returns rows but AB22 doesn't because AB999... comes second in the exclusions list.
The query should produce the a not not null result for either key...

Could you please help out with how the query can be amended?
I tried regexp_substr, but couldn't work out how to use the substring of product name as a pattern... Similarly, the connect by level from the aforementioned page, not sure how to loop it in so that we're only selecting from exclusions once..

Just to make it more fun, the full query has another table that keeps codes in the same format as above, so needs parsing the same way...

That query above is essentially aliased to 'q', such that

select from 
(<query above>) q,
manufacturers
where manufacturers.a = q.a and manufacturers.b = q.b ...
and <any of the codes in a code string in b> = a.code

Even when I get the exists to kind of run, this certainly does not....

Thank you ever so much!

select regexp_substr ( code, '[^/]+', 1, r.l ) val
from   exclusions,
       lateral (
         select level l from dual
         connect by level <= 
           length ( code ) - length ( replace ( code, '/' ) ) + 1
       ) r;

VAL     
AB345   
AB999   
QD523   

with exclusion_rws as (
  select regexp_substr ( code, '[^/]+', 1, r.l ) val
  from   exclusions,
         lateral (
           select level l from dual
           connect by level <= 
             length ( code ) - length ( replace ( code, '/' ) ) + 1
         ) r
) 
select p.*
from   products p
where  exists (
   select * from exclusion_rws er
   where  er.val = substr(p.name,1,instr(p.name,'*')-1)
);

KEY    NAME                 
AB47   AB345*Some name      
AB47   AB345*Some name      
AB88   AB345*Some name      
AB22   AB999*Another name