how to write a loop query with oracle

Question

I have this table

id	i1	i2
Y001	x1	a1
Y001	x1	a2
Y001	a1	a5
Y001	a2	a3
Y001	a3	a4

The relationship between i1 and i2 is i2 contains i1,so a1 contains x1, a5 contains a1, I want to get the final relationship for all items.

The expected result is

id	i	i_final
Y001	x1	a5
Y001	x1	a4
Y001	a1	a5
Y001	a2	a4
Y001	a3	a4

If it's possible to have duplicates within i1 column then what path woul you choose while descending the hierarchy? Suppose there's another row with i1 = 'x0' and i2 = 'x1' — astentx
– astentx, Commented Jul 11, 2023 at 7:43

MT0 · Accepted Answer · 2023-07-11 07:58:56Z

1

You do not want a "loop". You want to use a hierarchical query and find the leaves of the hierarchy tree for each root:

SELECT id,
       CONNECT_BY_ROOT i1 AS i1,
       i2
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY
       PRIOR i2 = i1
AND    PRIOR id = id -- Not sure if this is needed as you only have one id value

Which, for the sample data:

CREATE TABLE table_name (id, i1, i2) AS
SELECT 'Y001', 'x1', 'a1' FROM DUAL UNION ALL
SELECT 'Y001', 'x1', 'a2' FROM DUAL UNION ALL
SELECT 'Y001', 'a1', 'a5' FROM DUAL UNION ALL
SELECT 'Y001', 'a2', 'a3' FROM DUAL UNION ALL
SELECT 'Y001', 'a3', 'a4' FROM DUAL;

Outputs:

ID	I1	I2
Y001	a1	a5
Y001	a2	a4
Y001	a3	a4
Y001	x1	a5
Y001	x1	a4

fiddle

answered Jul 11, 2023 at 7:58

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

how to write a loop query with oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related