CODEWITHSUNDEEP
sqloracle-databaserecursive-query
Fogarasi Norbert
Fogarasi Norbert

Reputation: 672

Using multiple UNION ALL statements in WITH clause

My table looks like this:

CREATE TABLE gyerek (
    gyereke INT,
    szulonek INT
);

I'm trying to use multiple UNION ALL statements in my WITH clause:

WITH testver (x, y) AS (
    SELECT gy1.gyereke, gy2.gyereke
    FROM gyerek gy1, gyerek gy2
    WHERE gy1.szulonek = gy2.szulonek
    AND gy1.gyereke <> gy2.gyereke
), rokon (x, y) AS (
    SELECT * FROM testver
    UNION ALL
    (SELECT r1.x, gy1.gyereke
    FROM rokon r1, gyerek gy1
    WHERE r1.y = gy1.szulonek)
    UNION ALL(SELECT gy1.gyereke, r1.y
    FROM rokon r1, gyerek gy1
    WHERE r1.x = gy1.szulonek)
)
SELECT * FROM rokon;

I get the following error:

ORA-32041: UNION ALL operation in recursive WITH clause must have only two branches

My question is: Is there a way to use multiple UNION ALL statements? If there isn't how can I achieve the unification of the last two SELECT statements.

Upvotes: 3

Views: 4365

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

In the recursion you want to go on with x on a y match and with y on an x match. Use CASE WHEN to check which one matches.

, rokon (x, y) AS (
  SELECT x, y FROM testver
  UNION ALL
  SELECT CASE WHEN gy1.szulonek = r1.x THEN r1.y ELSE r1.x END, gy1.gyereke
  FROM rokon r1
  JOIN gyerek gy1 ON gy1.szulonek IN (r1.x, r1.y)
)

Upvotes: 2

Related Questions