各位大侠们,我在写一个递归函数:
CREATE function [dbo].[F_USERPROJECT](@username varchar(100))
returns table
as
return(
with sub_table(prjid,code,userid,username)
as(
select distinct d.dutyid as prjid,'' as code,u.id as userid,u.username
from table1 a,table2 b,table3 c
where a.id = b.empid
and c.id = b.empid
and a.username = @username
union all
select p.id as prjid,p.code,u.userid,u.username
from t_pm_project p
left join sub_table u on u.projectid = p.deptid
where p.deptid in (select id from f_org(u.prjid) where code is null)
)
select distinct prjid,code,userid,username
from sub_table where code is not null or code <> ''
)
GO
上面的函数执行后报错:
消息 462,级别 16,状态 1,过程 F_USERPROJECT,第 5 行
在递归公用表表达式 'sub_table' 的递归部分不允许使用外部联接。
搞了半天了,没整明白哪里的错误,请高手们指教,在线等待...
CREATE function [dbo].[F_USERPROJECT](@username varchar(100))
returns table
as
return(
with sub_table(prjid,code,userid,username)
as(
select distinct d.dutyid as prjid,'' as code,u.id as userid,u.username
from table1 a,table2 b,table3 c
where a.id = b.empid
and c.id = b.empid
and a.username = @username
union all
select p.id as prjid,p.code,u.userid,u.username
from t_pm_project p
left join sub_table u on u.projectid = p.deptid
where p.deptid in (select id from f_org(u.prjid) where code is null)
)
select distinct prjid,code,userid,username
from sub_table where code is not null or code <> ''
)
GO
上面的函数执行后报错:
消息 462,级别 16,状态 1,过程 F_USERPROJECT,第 5 行
在递归公用表表达式 'sub_table' 的递归部分不允许使用外部联接。
搞了半天了,没整明白哪里的错误,请高手们指教,在线等待...
return @t table(prjid,code,userid,username)
as
(
with sub_table(prjid,code,userid,username)
as(
select distinct d.dutyid as prjid,'' as code,u.id as userid,u.username
from table1 a,table2 b,table3 c
where a.id = b.empid
and c.id = b.empid
and a.username = @username
union all
select p.id as prjid,p.code,u.userid,u.username
from t_pm_project p
left join sub_table u on u.projectid = p.deptid
where p.deptid in (select id from f_org(u.prjid) where code is null)
)
insert into @t
select distinct prjid,code,userid,username
from sub_table where code is not null or code <> ''
return @t
)
GO
returns table
as
return(
with sub_table(prjid,code,userid,username)
as(
select distinct d.dutyid as prjid,'' as code,u.id as userid,u.username
from table1 a,table2 b,table3 c
where a.id = b.empid
and c.id = b.empid
and a.username = @username
union all
select p.id as prjid,p.code,u.userid,u.username
from t_pm_project p
join sub_table u on u.projectid = p.deptid
where p.deptid in (select id from f_org(u.prjid) where code is null)
)
select distinct prjid,code,userid,username
from sub_table where code is not null or code <> ''
)
GO