PLSQL - Connect By Prior (many to many)

Question

I have a query that uses a connect by statement to order the recursive data. The problem I have is that there is occasionally a one to many or a many to one relationship and I dont know how to deal with it.

  SELECT  *  
FROM (SELECT * FROM bdTable WHERE parentek = t_parKey)
START WITH source is null
CONNECT BY PRIOR target = source

So to explain. I have a source and target columns. About 99% of the time these are a single unique ID. Unfortunately the other 1% of the time there are a grouping of IDs in one of the columns. This table is a flat representation of a flowchart type tool, so there are splits and decisions which can have many outputs and merges which can have many inputs.

To deal with this in loading the data for the table, the unique IDs are concatenated together using the listagg function. So I end up with a Target value of something like '1254143,2356334,6346436,3454363,3462354,442356'.

So when my connect by statement is executed, it works perfectly until it comes to one of these scenarios, at which point it just stops (which is expected of course).
I thought I might be able to use IN or INSTR in some way to get it working, but haven't had any luck yet and I can't find anything on it online.

Any help would be appreciated.....

Could you provide some sort of schema? Also, concatenated "unique" ids is general not the best strategy, you may want to create your own unique ID instead. — dougEfresh, Jul 02 '13 at 14:38
Although I dont see creating my own unique ID as an answer, it did make me think of a possible solution to this. Let it with me for now. Thanks anyway. — user2542663, Jul 02 '13 at 14:46

score 0 · Answer 1 · edited May 23 '17 at 10:32

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.

PLSQL - Connect By Prior (many to many)

1 Answers1