Recursive query - Oracle

Question

I have this data and expected result:

    Data                Expected Result 
No  A   B                  A    B
1   10  500               10    500                          
2   10  c=20              20    400
3   20  400               30    600
4   30  600               30    700
5   30  c=40              30    800
6   30  c=50              40    700
7   40  700               50    900
8   50  c=60              60    900
9   60  c=70              70    900
10  70  900               10    400

I need to perform a self join and get the result.

For line number 1 the expected result is same as the row.
For line number 2, I need to take the substring of column B (c=20) as 20 and join with column B and get the result as 400.
Lines number 5 and 6 need to substring column B and get the result from column A.

I tried a recursive query, but still am not getting the expected result.

with rec(A, B, nested) as
(
select A, B, case when instr(B, 'C=') != 0 then substr(B, instr(B, 'C=')) as nested
from table
union all
select A, rec.B from table
inner join rec
on (table.A = rec.nested)
)
select A, B, nested from rec;

You have edited the question and changed the expected results; please edit the question again and explain how you get to those results as it is unclear how you get an expected answer of 300 | 800 when 800 does not appear in the input. — MT0
– MT0, Commented Oct 11, 2022 at 11:05
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory minimal reproducible example & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a minimal reproducible example. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. How to Ask Help center — philipxy
– philipxy, Commented Oct 11, 2022 at 21:31

MT0 · Accepted Answer · 2022-10-11 11:13:07Z

3

Answer for the initial version of the question

You do not need a recursive query. To get your desired output you just need to exclude the rows where B starts with c=:

SELECT a, b
FROM   table_name
WHERE b NOT LIKE 'c=%';

Which, for the sample data:

CREATE TABLE table_name (no, a, b) AS
  SELECT 1, 10, '500'  FROM DUAL UNION ALL
  SELECT 2, 10, 'c=20' FROM DUAL UNION ALL
  SELECT 3, 20, '400'  FROM DUAL UNION ALL
  SELECT 4, 30, '600'  FROM DUAL UNION ALL
  SELECT 5, 30, 'c=40' FROM DUAL UNION ALL
  SELECT 6, 30, 'c=50' FROM DUAL UNION ALL
  SELECT 7, 40, '700'  FROM DUAL UNION ALL
  SELECT 8, 50, '800'  FROM DUAL;

Outputs your desired output:

A	B
10	500
20	400
30	600
40	700
50	800

fiddle

Answer for the 3rd edit of the question

You can use a hierarchical query:

SELECT DISTINCT
       CONNECT_BY_ROOT a AS a,
       b
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY
       PRIOR b LIKE 'c=%'
AND    PRIOR SUBSTR(b, 3) = a
ORDER BY a, b;

Which, for the sample data:

CREATE TABLE table_name (no, a, b) AS
  SELECT  1, 10, '500'  FROM DUAL UNION ALL
  SELECT  2, 10, 'c=20' FROM DUAL UNION ALL
  SELECT  3, 20, '400'  FROM DUAL UNION ALL
  SELECT  4, 30, '600'  FROM DUAL UNION ALL
  SELECT  5, 30, 'c=40' FROM DUAL UNION ALL
  SELECT  6, 30, 'c=50' FROM DUAL UNION ALL
  SELECT  7, 40, '700'  FROM DUAL UNION ALL
  SELECT  8, 50, 'c=60' FROM DUAL UNION ALL
  SELECT  9, 60, 'c=70' FROM DUAL UNION ALL
  SELECT 10, 70, '900'  FROM DUAL;

Outputs:

A	B
10	400
10	500
20	400
30	600
30	700
30	900
40	700
50	900
60	900
70	900

fiddle

edited Oct 11, 2022 at 11:13

answered Oct 11, 2022 at 10:34

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jim Macaulay Over a year ago

Thanks for the quick answer, but i have to check if the value in Column A is available or no and then recursively get the data

Jim Macaulay Over a year ago

Thanks for your efforts. But it is failing with, ORA-01436: CONNECT BY loop in user data

MT0 Over a year ago

@JimMacaulay The query runs for your sample data without that error in the fiddle linked in the answer. If your sample data is not representative of your actual data then you should edit your question to ensure that the sample data is representative of your actual data; otherwise, how do you expect other people to accurately answer your question?

MT0 Over a year ago

@JimMacaulay If there is a loop in your data then add a NOCYCLE clause to the hierarchical query.

Collectives™ on Stack Overflow

Recursive query - Oracle

1 Answer 1

Answer for the initial version of the question

Answer for the 3rd edit of the question

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Answer for the initial version of the question

Answer for the 3rd edit of the question

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related