Search code examples
oracle11grecursive-query

Is a recursive query possible?

An Oracle SQL question that seems simple to me, but I just can't do it (without manually making several successive queries)... I would like to use a SQL "recursive" approach or any other method that would avoid me to manually use "n" times the same query.

Here is a very simplified example of my data:

row ID Value Result
1 135 AAA AAA
2 246 BBB BBB
3 357 135 AAA
4 468 357 AAA
5 578 EEE EEE

If a value refers to an ID, then we need to fetch the value of that ID. If the value of an ID is another ID, then you have to keep looking for an "true" value. If the value is not an ID, then we can take it directly.

The column "Result" gives the expected result:

For the row 3, you have to look for the value of the ID 135.

For the line 4, you have to get the value of the ID 357, which corresponds to the value of the ID 135.

Here, what I don't want to do:

with my_data as(
    select '135' as id, 'AAA' as value from dual union all
    select '246', 'BBB' from dual union all
    select '357', '135' from dual union all
    select '468', '357' from dual union all
    select '578', 'EEE' from dual
)
,step as(
    select d1.*
          ,nvl((select d2.value from my_data d2 where d1.value=d2.id),d1.value) as step_1
    from my_data d1
)
-- steps to do again and again...
select step.*
      ,nvl((select my_data.value from my_data where step.step_1=my_data.id),step.step_1) as step_n
from step
order by 1
;

Thank you for your help.

In addition to Carlos S anwser:

It does exactly what I want in my "very simplified" example. However, with a sub-set of real data, there is something wrong (a little too much data). On the other hand, if I remove the results that refer to IDs, it gives exactly what I want. I can't find the problem.

Note: as shown in my initial example, IDs will always start with a number and a "real" value will always start with a letter. So the regex strategy is "ok".

Here is a real example:

with my_data as(
    select '116554226_2' as id, '116554226_1' as value from dual union all
    select '119675285_2' as id, '119675285_1' as value from dual union all
    select '119675285_3' as id, '119675285_2' as value from dual union all
    select '13656777_1' as id, '119675471_1' as value from dual union all
    select '13656777_5001' as id, '119675471_1' as value from dual union all
    select '13656777_2' as id, '13656777_1' as value from dual union all
    select '13656155_1' as id, '13657581_1' as value from dual union all
    select '13657581_2' as id, '13657581_1' as value from dual union all
    select '13657015_1' as id, '13657759_1' as value from dual union all
    select '13657759_2' as id, '13657759_1' as value from dual union all
    select '116554226_1' as id, '471502681_1' as value from dual union all
    select '462721769_1' as id, 'O7X5J' as value from dual union all
    select '471502681_1' as id, 'T3L8L' as value from dual union all
    select '119675471_1' as id, 'T8Q0G' as value from dual union all
    select '119675471_5001' as id, 'T8Q0G' as value from dual union all
    select '116555133_1' as id, 'T9J2Q' as value from dual union all
    select '13657581_1' as id, 'U5H5Z' as value from dual union all
    select '119674049_1' as id, 'Y5G7V' as value from dual union all
    select '13657759_1' as id, 'Z0Y9C' as value from dual union all
    select '119675285_1' as id, 'Z7E0D' as value from dual
)
SELECT my_data.*, CONNECT_BY_ROOT value result
FROM   my_data
CONNECT BY PRIOR id = value 
START WITH REGEXP_LIKE(value,'[^0-9]')
;

The boxed data is unwanted, otherwise everything below it is ok. enter image description here

Solution

  • I think this query may help you:

    with my_data as(
  select '135' as id, 'AAA' as value from dual union all
  select '246', 'BBB' from dual union all
  select '357', '135' from dual union all
  select '468', '357' from dual union all
  select '578', 'EEE' from dual
)
SELECT rownum, id, value, CONNECT_BY_ROOT value result
FROM   my_data
CONNECT BY PRIOR id = value 
START WITH REGEXP_LIKE(value,'[^0-9]')
    ROWNUM ID VALUE RESULT
    1 135 AAA AAA
    2 357 135 AAA
    3 468 357 AAA
    4 246 BBB BBB
    5 578 EEE EEE