Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using CTE in Oracle

708063Jun 24 2009 — edited Jun 24 2009
How it is possible to make it in Oracle?

SQL Server:

WITH cte (col1, col2) AS
(
SELECT col1, col2
FROM dbo.tb1
WHERE col1 = 12
UNION ALL
SELECT c.col1, c.col2
FROM dbo.tb1 AS c INNER JOIN cte AS p ON c.col2 = p.col1
where c.col1 = c.col2
)
SELECT * FROM cte

It is probably necessary to use START WITH ... CONNECT BY PRIOR?

I tried to make so:

WITH cte AS(SELECT col1 col1, col2 col2
FROM tb12
WHERE col1 = 12
UNION ALL
SELECT c.col1 col1, c.col2 col2
FROM tb12 c INNER JOIN cte p ON c.col2 = p.col1
where c.col1 = c.col2)
SELECT * FROM cte
order by lvl NULLS FIRST;

Result:
ORA-32031: illegal reference of a query name in WITH clause
This post has been answered by Boneist on Jun 24 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2009
Added on Jun 24 2009
29 comments
62,897 views