If you want to join target
and source
with use of some logic, based on intersection of set of values, listed in each column, then most reliable way to do so is to split strings to collections and operate on collection from prior row and collection from current row to build a tree.
There are a number of techniques to build collection from separated string in Oracle, one of them illustrated in this answer to another question.
Create required collection type:
create or replace type TIdList as table of varchar2(100);
Inner select in your case would look like this:
SELECT
t.*,
(
cast(multiset( -- Convert set of values into collection
select -- Build list of values from separated string
substr(
source,
decode( level, 1, 1, instr(source,',',1,level-1)+1 ),
decode( instr(source,',',1,level), 0, length(source)+1, instr(source,',',1,level) )
-
decode( level, 1, 1, instr(source,',',1,level-1)+1 )
) code
from dual
start with source is not null
connect by instr(source,',',1,level-1) > 0
) as TIdList )
) source_id_list,
(
cast(multiset( -- Convert set of values into collection
select -- Build list of values from separated string
substr(
target,
decode( level, 1, 1, instr(target,',',1,level-1)+1 ),
decode( instr(target,',',1,level), 0, length(target)+1, instr(target,',',1,level) )
-
decode( level, 1, 1, instr(target,',',1,level-1)+1 )
) code
from dual
start with target is not null
connect by instr(target,',',1,level-1) > 0
) as TIdList )
) target_id_list
FROM bdTable t
WHERE t.parentek = t_parKey
Because I don't know which column (source
or target
) contains separated list, I include column for each.
After building collection(s) it's possible to use multiset operators and available test functions to match target with source. E.g.
with inner_query as (
SELECT
t.*,
(
cast(multiset( -- Convert set of values into collection
select -- Build list of values from separated string
substr(
source,
decode( level, 1, 1, instr(source,',',1,level-1)+1 ),
decode( instr(source,',',1,level), 0, length(source)+1, instr(source,',',1,level) )
-
decode( level, 1, 1, instr(source,',',1,level-1)+1 )
) code
from dual
start with source is not null
connect by instr(source,',',1,level-1) > 0
) as TIdList )
) source_id_list,
(
cast(multiset( -- Convert set of values into collection
select -- Build list of values from separated string
substr(
target,
decode( level, 1, 1, instr(target,',',1,level-1)+1 ),
decode( instr(target,',',1,level), 0, length(target)+1, instr(target,',',1,level) )
-
decode( level, 1, 1, instr(target,',',1,level-1)+1 )
) code
from dual
start with target is not null
connect by instr(target,',',1,level-1) > 0
) as TIdList )
) target_id_list
FROM bdTable t
WHERE t.parentek = t_parKey
)
select
level lvl,
tree_list.*
from
inner_query tree_list
start with
source is null
connect by
nvl(cardinality(prior target_id_list MULTISET INTERSECT source_id_list),0) > 0
If only one column can contain list of values, then MEMBER OF
construct are useful.