Using CTE in Oracle
708063Jun 24 2009 — edited Jun 24 2009How 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