Recursion In Oracle

Question

I have the following table in an oracle:

Parent(arg1, arg2)

and I want the transitive closure of the relation parent. That is, I want the following table

Ancestor(arg1, arg2)

How is this possible in Oracle?

I am doing the following:

WITH Ancestor(arg1, arg2)  AS (

  SELECT p.arg1, p.arg2 from parent p
  UNION
  SELECT p.arg1 , a.arg2 from parent p,  Ancestor a 
  WHERE p.arg2 = a.arg1

)

SELECT DISTINCT * FROM Ancestor;

I get the error

*Cause:    column aliasing in WITH clause is not supported yet
*Action:   specify aliasing in defintion subquery and retry
Error at Line: 1 Column: 20

How can I solve this without column aliasing?

Richard Dingwall · Accepted Answer · 2012-03-21 23:01:11Z

27

WITH    Ancestor(arg1, arg2) AS
        (
        SELECT  p.arg1, p.arg2
        FROM    parent p
        WHERE   arg2 NOT IN
        (
            SELECT  arg1
            FROM    parent
        )

        UNION ALL

        SELECT  p.arg1, a.arg2
        FROM    Ancestor a 
        JOIN    parent p
        ON      p.arg2 = a.arg1
        )
SELECT  *
FROM    Ancestor

Oracle only supports recursive CTE since 11g Release 2.

In earlier versions, use CONNECT BY clause:

SELECT  arg1, CONNECT_BY_ROOT arg2
FROM    parent
START WITH
        arg2 NOT IN
        (
        SELECT  arg1
        FROM    parent
        )
CONNECT BY
        arg2 = PRIOR arg1

edited Mar 21, 2012 at 23:01

Richard Dingwall

2,7521 gold badge31 silver badges32 bronze badges

answered Jan 11, 2011 at 16:22

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

1 Comment

user330315 Over a year ago

11g Release 2 to be precise

FrustratedWithFormsDesigner · Accepted Answer · 2011-01-11 16:22:47Z

1

Oracle allows recursive queries. See: http://www.adp-gmbh.ch/ora/sql/connect_by.html

Of course, these usually assume the hierarchical data is all in one table. Splitting it into separate tables makes things complicated.

edited Jan 11, 2011 at 16:22

answered Jan 11, 2011 at 16:17

FrustratedWithFormsDesigner

27.7k31 gold badges151 silver badges211 bronze badges

Collectives™ on Stack Overflow

Recursion In Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related