我一个SQL 语句
with Hierarchy as
(
select a.SourceID, a.ChildOf
from Concept a
where a.Source='ML' and a.Band='TC' and a.SourceID='TL-10'
union all
select b.SourceID, b.ChildOf
from Concept b
inner join Hierarchy c
on b.ChildOf=c.SourceID
where b.Source='ML' and b.Band='TC'
)
Select SourceID
From Hierarchy
这个在sql server中正常执行,但在oracle中有错,错误提示为
ORA-32031: illegal reference of a query name in WITH clause
哪位大侠有解决的办法?
with Hierarchy as
(
select a.SourceID, a.ChildOf
from Concept a
where a.Source='ML' and a.Band='TC' and a.SourceID='TL-10'
union all
select b.SourceID, b.ChildOf
from Concept b
inner join Hierarchy c
on b.ChildOf=c.SourceID
where b.Source='ML' and b.Band='TC'
)
Select SourceID
From Hierarchy
这个在sql server中正常执行,但在oracle中有错,错误提示为
ORA-32031: illegal reference of a query name in WITH clause
哪位大侠有解决的办法?
with tb as --换个名字试试 你里面有个表名是 Hierarchy了
(
select a.SourceID, a.ChildOf
from Concept a
where a.Source='ML' and a.Band='TC' and a.SourceID='TL-10'
union all
select b.SourceID, b.ChildOf
from Concept b
inner join Hierarchy c
on b.ChildOf=c.SourceID
where b.Source='ML' and b.Band='TC'
)
Select SourceID
From tb
--这是一个cte自引用问题:
--试试这个,
--或许你可以参考:http://forums.oracle.com/forums/thread.jspa?threadID=919467
with Hierarchy as(
select a.SourceID SourceID, a.ChildOf ChildOf from Concept a
where a.Source='ML' and a.Band='TC' and a.SourceID='TL-10' union all
select b.SourceID SourceID, b.ChildOf ChildOf
from Concept b inner join Hierarchy c on b.ChildOf=c.SourceID
where b.Source='ML' and b.Band='TC' )
Select SourceID
From Hierarchy
其中XXXX是一个公用表表达式,该表达式在使用上与表变量类似,你是不是自查询和XXXX没有任何关系,你改下XXXX的名字试试,不会影响你的自查询,应该是名字重复了。
不过无论ms-sqlserver 也好oracle 也好
with XXXX as 的使用方式和原理是一样的。
(
select a.SourceID, a.ChildOf
from Concept a
where a.Source='ML' and a.Band='TC' and a.SourceID='TL-10'
union all
select b.SourceID, b.ChildOf
from Concept b
)
Select SourceID
From Hierarchy
inner join Hierarchy c
on b.ChildOf=c.SourceID
where b.Source='ML' and b.Band='TC'