declare @FNumber1 varchar(50)
select a.fbomnumber , a.fversion , b.fnumber as fshortnumber,c.fitemid,d.fnumber,d.fname into #yinbom1
from icbom a
left join t_icitem b on a.fitemid=b.fitemid
left join icbomchild c on a.finterid=c.finterid
left join t_icitem d on c.fitemid=d.fitemid
where a.fitemid in (select fitemid from t_icitem where fname like '%@FNumber1%') and a.fusestatus=1072 and a.fstatus=1执行报错服务器: 消息 156,级别 15,状态 1,行 9
在关键字 'left' 附近有语法错误。不知道是哪里的问题
select a.fbomnumber , a.fversion , b.fnumber as fshortnumber,c.fitemid,d.fnumber,d.fname into #yinbom1
from icbom a
left join t_icitem b on a.fitemid=b.fitemid
left join icbomchild c on a.finterid=c.finterid
left join t_icitem d on c.fitemid=d.fitemid
where a.fitemid in (select fitemid from t_icitem where fname like '%@FNumber1%') and a.fusestatus=1072 and a.fstatus=1执行报错服务器: 消息 156,级别 15,状态 1,行 9
在关键字 'left' 附近有语法错误。不知道是哪里的问题
select a.fbomnumber , a.fversion , b.fnumber as fshortnumber,c.fitemid,d.fnumber,d.fname into #yinbom1
from icbom a
left join t_icitem b on a.fitemid=b.fitemid
left join icbomchild c on a.finterid=c.finterid
left join t_icitem d on c.fitemid=d.fitemid
where a.fitemid in (select fitemid from t_icitem where fname like '%'+@FNumber1+'%') and a.fusestatus=1072 and a.fstatus=1
select a.fbomnumber , a.fversion , b.fnumber as fshortnumber,c.fitemid,d.fnumber,d.fname into #yinbom1
from icbom a
left join t_icitem b on a.fitemid=b.fitemid
left join icbomchild c on a.finterid=c.finterid
left join t_icitem d on c.fitemid=d.fitemid
where a.fitemid in (select fitemid from t_icitem where fname like '%'+@FNumber1+'%') and a.fusestatus=1072 and a.fstatus=1
select a.fbomnumber ,
a.fversion ,
b.fnumber as fshortnumber,
c.fitemid,d.fnumber,
d.fname into #yinbom1
from icbom a
left join t_icitem b on a.fitemid=b.fitemid
left join icbomchild c on a.finterid=c.finterid
left join t_icitem d on c.fitemid=d.fitemid
where a.fitemid in (select fitemid from t_icitem where fname like '%'+@FNumber1+'%') and a.fusestatus=1072 and a.fstatus=1
--没有发现语法错误
'%@FNumber1%'->'%'+@FNumber1+'%'
--select * from ICBOMChild
--select * from sys.objects where name like '%BOM%' and type='u'
--select * from ICBom;with t as
(
select Finterid,fparentid,afitemid,bfitemid,1 as lvl
from (
select a.finterid,a.fparentid,a.fitemid afitemid,b.fitemid bfitemid,a.fbomnumber
from ICBOM a
join icbomchild b
on a.finterid=b.finterid and fusestatus=1072
) as tb
--where fbomnumber='CP.07.001'
union all
select a.finterid,a.fparentid,a.afitemid,a.bfitemid,b.lvl+1
from (
select a.finterid,a.fparentid,a.fitemid afitemid,b.fitemid bfitemid
from ICBOM a
join icbomchild b
on a.finterid=b.finterid and fusestatus=1072
) as a join t as b on a.afitemid=b.bfitemid
),t1 as
(
select
replicate('.',lvl)+ltrim(lvl) lvl,
a.FNumber afnumber,b.fnumber bfnumber,b.fname,b.fmodel,
case b.ferpclsid when 1 then '外购' when 2 then '自制' when 3 then '委外加工' end 物料属性
from t
join t_icitem a
on t.afitemid=a.fitemid
join t_icitem b
on t.bfitemid=b.fitemid
)
select rn=row_number()over(order by getdate()),*
from t1 a
where afnumber='CP.07.001'这是我以前写过的SQL2005的