CODEWITHSUNDEEP
sqloracle-database
Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

connect by prior delivers unexpected results

This select statement delivers strange results: 

with data(id, seqno, cs_prev_seqno, descr) as
 (select 1, 1, 0, 'Id 1 Step1' from dual
  union all
  select 1, 2, 1, 'Id 1 Step2' from dual
  union all
  select 1, 3, 2, 'Id 1 Step3' from dual
  union all
  select 1, 4, 1, 'Id 1 Step4' from dual      
  union all
  select 2, 1, 0, 'Id 2 Step1' from dual
  union all
  select 2, 2, 1, 'Id 2 Step2' from dual)

select id,
       sys_connect_by_path(seqno, '/') as path,
       seqno,
       cs_prev_seqno,
       descr,
       level
  from data
 where id = 1
connect by prior seqno = cs_prev_seqno
 start with cs_prev_seqno = 0;

I expected that the connect by is only done for rows with id=1, but the results are:

id path   seq seq_prev descr       level
1 /1      1   0        Id 1 Step1  1
1 /1/2    2   1        Id 1 Step2  2
1 /1/2/3  3   2        Id 1 Step3  3
1 /1/2/3  3   2        Id 1 Step3  3
1 /1/4    4   1        Id 1 Step4  2
1 /1/2    2   1        Id 1 Step2  2
1 /1/2/3  3   2        Id 1 Step3  3
1 /1/2/3  3   2        Id 1 Step3  3
1 /1/4    4   1        Id 1 Step4  2

I.E. first of all the connect by is done for all rows, afterwards the result is filtered by id.

As a workaround, the following statement delivers the correct results:

with data(id,
seqno,
cs_prev_seqno,
descr) as
 (select 1, 1, 0, 'Id 1 Step1'
    from dual
  union all
  select 1, 2, 1, 'Id 1 Step2'
    from dual
  union all
  select 1, 3, 2, 'Id 1 Step3'
    from dual
  union all
  select 1, 4, 1, 'Id 1 Step4'
    from dual

  union all
  select 2, 1, 0, 'Id 2 Step1'
    from dual
  union all
  select 2, 2, 1, 'Id 2 Step2'
    from dual

  )

,
data2 as
 (select d.id,
         d.seqno,
         d.cs_prev_seqno,
         d.id || '.' || d.seqno as id_seqno,
         d.id || '.' || d.cs_prev_seqno as cs_id_prev_seqno,
         d.descr
    from data d)

select id,
       sys_connect_by_path(seqno, '/') as path,
       seqno,
       cs_prev_seqno,
       descr,
       level
  from data2
 where id = 1
connect by prior id_seqno = cs_id_prev_seqno
 start with cs_prev_seqno = 0;

-->

id path   seq seq_prev descr       level
1 /1      1   0        Id 1 Step1  1
1 /1/2    2   1        Id 1 Step2  2
1 /1/2/3  3   2        Id 1 Step3  3
1 /1/4    4   1        Id 1 Step4  2

But I guess there should be an easier way to achieve this? Thanks in advance!

Upvotes: 2

Views: 38

Answers (2)

MT0
MT0

Reputation: 168623

Use CONNECT BY to restrict connections to the same ID:

CONNECT BY PRIOR id = id
       AND PRIOR seqno = cs_id_prev_seqno

Which would make your query:

SELECT     id,
           SYS_CONNECT_BY_PATH(seqno, '/') AS path,
           seqno,
           cs_prev_seqno,
           descr,
           level
FROM       data d
WHERE      id = 1
CONNECT BY PRIOR id = id
       AND PRIOR seqno = cs_id_prev_seqno
START WITH cs_prev_seqno = 0;

I expected that the connect by is only done for rows with id=1

As you found out, this is not true. The query will find all the rows where:

  • The root row has cs_prev_seqno = 0 (as per the START WITH clause);
  • The current row has id = 1 (the WHERE clause); and
  • That, at each step, from the root to the current row the CONNECT BY clause is satisfied.

If the CONNECT BY clause does not specify that the ID should be constant then it will not be checked on those intervening steps.

Upvotes: 2

Aleksej
Aleksej

Reputation: 22969

If I understand well, you can try limiting dataset before applying the CONNECT BY, for example:

with data(id, seqno, cs_prev_seqno, descr) as
 (select 1, 1, 0, 'Id 1 Step1' from dual union all
  select 1, 2, 1, 'Id 1 Step2' from dual union all
  select 1, 3, 2, 'Id 1 Step3' from dual  union all
  select 1, 4, 1, 'Id 1 Step4' from dual union all
  select 2, 1, 0, 'Id 2 Step1' from dual  union all
  select 2, 2, 1, 'Id 2 Step2' from dual)
select id,
       sys_connect_by_path(seqno, '/') as path,
       seqno,
       cs_prev_seqno,
       descr,
       level
  from ( select * from data where id = 1)
connect by prior seqno = cs_prev_seqno
 start with cs_prev_seqno = 0;

You could even use your condition in the CONNECT BY clause:

with data(id, seqno, cs_prev_seqno, descr) as
 (select 1, 1, 0, 'Id 1 Step1' from dual union all
  select 1, 2, 1, 'Id 1 Step2' from dual union all
  select 1, 3, 2, 'Id 1 Step3' from dual  union all
  select 1, 4, 1, 'Id 1 Step4' from dual union all
  select 2, 1, 0, 'Id 2 Step1' from dual  union all
  select 2, 2, 1, 'Id 2 Step2' from dual)
select id,
       sys_connect_by_path(seqno, '/') as path,
       seqno,
       cs_prev_seqno,
       descr,
       level
from data where id = 1
connect by prior seqno = cs_prev_seqno
         and prior id = 1
 start with cs_prev_seqno = 0;

Upvotes: 1

Related Questions