这是SQL的代码:
1、前提,变量字段为:cInvCode,就是当我输入字段的内容,如:A050500532、执行的是以下条件,例A05050053
SELECT d.cInvCode,d.cInvName,a.Version ,a.VersionDesc,a.VersionEffDate, c.free1 ,d.cInvStd,d.cInvAddCode ,e.cComUnitName,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate FROM bom_bom a inner join bom_parent b on a.bomid =b.bomid INNER JOIN bas_part c on b.parentId=c.partId INNER JOIN inventory d on c.invcode=d.cinvcode INNER JOIN ComputationUnit e on d.cComUnitCode=e.cComunitCode WHERE 1=1 and d.cInvcode >= 'A05050053' and d.cInvcode <= 'A05050053' Order by d.cInvCode,a.Version desc3、执行2后,将其结果字段为:InvCode,version进行第二次查询,例:c.InvCode='A05050053' and a.version='10'
Select a.bomid from bom_bom a inner join bom_parent b on a.bomid =b.bomid inner join bas_part c on b.parentId=c.partId where c.InvCode='A05050053' and a.version='10'4、执行3后,将其得到的字段为:bomid的值进行第三次查询,例:bomid=3955
Select a.SortSeq,a.OpSeq,c.cInvcode,c.cInvName,c.cInvStd, c.cInvAddCode,d.cComUnitName,a.BaseQtyN,a.BaseQtyD ,a.CompScrap,Qty=a.BaseQtyN/a.BaseQtyD ,a.OpComponentId ,b.Free1 FROM bom_opcomponent a inner join bas_part b on a.componentId=b.partId INNER JOIN inventory c on b.invcode=c.cinvcode INNER JOIN ComputationUnit d on c.cComUnitCode=d.cComunitCode Where a.bomid = 3955 Order by a.SortSeq
1、前提,变量字段为:cInvCode,就是当我输入字段的内容,如:A050500532、执行的是以下条件,例A05050053
SELECT d.cInvCode,d.cInvName,a.Version ,a.VersionDesc,a.VersionEffDate, c.free1 ,d.cInvStd,d.cInvAddCode ,e.cComUnitName,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate FROM bom_bom a inner join bom_parent b on a.bomid =b.bomid INNER JOIN bas_part c on b.parentId=c.partId INNER JOIN inventory d on c.invcode=d.cinvcode INNER JOIN ComputationUnit e on d.cComUnitCode=e.cComunitCode WHERE 1=1 and d.cInvcode >= 'A05050053' and d.cInvcode <= 'A05050053' Order by d.cInvCode,a.Version desc3、执行2后,将其结果字段为:InvCode,version进行第二次查询,例:c.InvCode='A05050053' and a.version='10'
Select a.bomid from bom_bom a inner join bom_parent b on a.bomid =b.bomid inner join bas_part c on b.parentId=c.partId where c.InvCode='A05050053' and a.version='10'4、执行3后,将其得到的字段为:bomid的值进行第三次查询,例:bomid=3955
Select a.SortSeq,a.OpSeq,c.cInvcode,c.cInvName,c.cInvStd, c.cInvAddCode,d.cComUnitName,a.BaseQtyN,a.BaseQtyD ,a.CompScrap,Qty=a.BaseQtyN/a.BaseQtyD ,a.OpComponentId ,b.Free1 FROM bom_opcomponent a inner join bas_part b on a.componentId=b.partId INNER JOIN inventory c on b.invcode=c.cinvcode INNER JOIN ComputationUnit d on c.cComUnitCode=d.cComunitCode Where a.bomid = 3955 Order by a.SortSeq
@version bigint ,
@InvCode nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
with tb1 as
(
SELECT d.cInvCode,d.cInvName,a.Version ,
a.VersionDesc,a.VersionEffDate, c.free1 ,d.cInvStd,d.cInvAddCode ,
e.cComUnitName,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate
FROM bom_bom a inner join bom_parent b on a.bomid =b.bomid INNER JOIN
bas_part c on b.parentId=c.partId INNER JOIN inventory d
on c.invcode=d.cinvcode INNER JOIN ComputationUnit e
on d.cComUnitCode=e.cComunitCode
WHERE 1=1 and d.cInvcode >= 'A05050053' and d.cInvcode <= 'A05050053'
)
,tb2 as
(
Select a.bomid ,c.InvCode, a.version from bom_bom a inner join bom_parent b on a.bomid =b.bomid inner join bas_part c on b.parentId=c.partId
)
,
tb3 as
(
Select a.SortSeq,a.OpSeq,c.cInvcode,c.cInvName,c.cInvStd, c.cInvAddCode,d.cComUnitName,a.BaseQtyN,a.BaseQtyD ,
a.CompScrap,Qty=a.BaseQtyN/a.BaseQtyD ,a.OpComponentId ,b.Free1,a.bomid FROM bom_opcomponent a inner join bas_part b
on a.componentId=b.partId INNER JOIN inventory c
on b.invcode=c.cinvcode INNER JOIN ComputationUnit d on c.cComUnitCode=d.cComunitCode
)
select * from tb3 inner join tb2 on tb3.bomid=tb2.bomid
inner join tb1 on tb2.version=tb1.version and tb2.InvCode=tb1.InvCode
where tb1.version=@version and tb1.InvCode=@InvCode
end
go
@version bigint ,
@InvCode nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
with tb1 as
(
SELECT d.cInvCode,d.cInvName,a.Version ,
a.VersionDesc,a.VersionEffDate, c.free1 ,d.cInvStd,d.cInvAddCode ,
e.cComUnitName,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate
FROM bom_bom a inner join bom_parent b on a.bomid =b.bomid INNER JOIN
bas_part c on b.parentId=c.partId INNER JOIN inventory d
on c.invcode=d.cinvcode INNER JOIN ComputationUnit e
on d.cComUnitCode=e.cComunitCode
)
,tb2 as
(
Select a.bomid ,c.InvCode, a.version from bom_bom a inner join bom_parent b on a.bomid =b.bomid inner join bas_part c on b.parentId=c.partId
)
,
tb3 as
(
Select a.SortSeq,a.OpSeq,c.cInvcode,c.cInvName,c.cInvStd, c.cInvAddCode,d.cComUnitName,a.BaseQtyN,a.BaseQtyD ,
a.CompScrap,Qty=a.BaseQtyN/a.BaseQtyD ,a.OpComponentId ,b.Free1,a.bomid FROM bom_opcomponent a inner join bas_part b
on a.componentId=b.partId INNER JOIN inventory c
on b.invcode=c.cinvcode INNER JOIN ComputationUnit d on c.cComUnitCode=d.cComunitCode
)
select * from tb3 inner join tb2 on tb3.bomid=tb2.bomid
inner join tb1 on tb2.version=tb1.version and tb2.InvCode=tb1.InvCode
where tb1.version=@version and tb1.InvCode=@InvCode
end
go
create PROCEDURE [dbo].[zkbom]
@version bigint ,
@InvCode nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
with tb1 as
(
SELECT d.cInvCode,d.cInvName,a.Version ,
a.VersionDesc,a.VersionEffDate, c.free1 ,d.cInvStd,d.cInvAddCode ,
e.cComUnitName,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate
FROM bom_bom a inner join bom_parent b on a.bomid =b.bomid INNER JOIN
bas_part c on b.parentId=c.partId INNER JOIN inventory d
on c.invcode=d.cinvcode INNER JOIN ComputationUnit e
on d.cComUnitCode=e.cComunitCode
)
,tb2 as
(
Select a.bomid ,c.InvCode, a.version from bom_bom a inner join bom_parent b on a.bomid =b.bomid inner join bas_part c on b.parentId=c.partId
)
,
tb3 as
(
Select a.SortSeq,a.OpSeq,c.cInvcode,c.cInvName,c.cInvStd, c.cInvAddCode,d.cComUnitName,a.BaseQtyN,a.BaseQtyD ,
a.CompScrap,Qty=a.BaseQtyN/a.BaseQtyD ,a.OpComponentId ,b.Free1,a.bomid FROM bom_opcomponent a inner join bas_part b
on a.componentId=b.partId INNER JOIN inventory c
on b.invcode=c.cinvcode INNER JOIN ComputationUnit d on c.cComUnitCode=d.cComunitCode
)
select * from tb3 inner join tb2 on tb3.bomid=tb2.bomid
inner join tb1 on tb2.version=tb1.version and tb2.InvCode=tb1.InvCode
where tb1.version=@version and tb1.InvCode=@InvCode
end
go
create PROCEDURE [dbo].[zkbom]
@version bigint ,
@InvCode nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
with tb1 as
(
SELECT d.cInvCode,d.cInvName,a.Version ,
a.VersionDesc,a.VersionEffDate, c.free1 ,d.cInvStd,d.cInvAddCode ,
e.cComUnitName,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate
FROM bom_bom a inner join bom_parent b on a.bomid =b.bomid INNER JOIN
bas_part c on b.parentId=c.partId INNER JOIN inventory d
on c.invcode=d.cinvcode INNER JOIN ComputationUnit e
on d.cComUnitCode=e.cComunitCode
)
,tb2 as
(
Select a.bomid ,c.InvCode, a.version from bom_bom a inner join bom_parent b on a.bomid =b.bomid inner join bas_part c on b.parentId=c.partId
)
,
tb3 as
(
Select a.SortSeq,a.OpSeq,c.cInvcode,c.cInvName,c.cInvStd, c.cInvAddCode,d.cComUnitName,a.BaseQtyN,a.BaseQtyD ,
a.CompScrap,Qty=a.BaseQtyN/a.BaseQtyD ,a.OpComponentId ,b.Free1,a.bomid FROM bom_opcomponent a inner join bas_part b
on a.componentId=b.partId INNER JOIN inventory c
on b.invcode=c.cinvcode INNER JOIN ComputationUnit d on c.cComUnitCode=d.cComunitCode
)
select * from tb3 inner join tb2 on tb3.bomid=tb2.bomid
inner join tb1 on tb2.version=tb1.version and tb2.InvCode=tb1.InvCode
where tb1.version=@version and tb1.InvCode=@InvCode
end
go
这个去掉?
嗯,是U8的BOM查询,你能帮忙测试一下这个存储过程是否可行吗?
消息 207,级别 16,状态 1,过程 zkbom,第 30 行
列名 'InvCode' 无效。
消息 207,级别 16,状态 1,过程 zkbom,第 31 行
列名 'InvCode' 无效。