a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout' end ) --就是这里错误
改为:
(@product = 'B' and a.move_type in ('decin','bomrtn')) or
(@product = 'A' and a.move_type in ('decout','mkout','prdmsy','prdout'))
改为:
(@product = 'B' and a.move_type in ('decin','bomrtn')) or
(@product = 'A' and a.move_type in ('decout','mkout','prdmsy','prdout'))
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or materialAS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and case @product when 'B' then ',decin,bomrtn,' when 'A' then ',decout,mkout,prdmsy,prdout,' end like '%,'+a.move_type+',%'select * into #tt2 from dbo.trade e
where e.typeflag = @productselect a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code
改为:
=============================================================
a.move_type in (
select case @product when 'B' then 'decin' end union
select case @product when 'B' then 'bomrtn' end union
select case @product when 'A' then 'decout' end union
select case @product when 'A' then 'mkout' end union
select case @product when 'A' then 'prdmsy' end union
select case @product when 'A' then 'prdout' end )
a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or 'mkout' or 'prdmsy' or 'prdout' end )
改为
(@product = 'B' and (a.move_type in ('decin','bomrtn')) or @product = 'A' and (a.move_type in ('decout','mkout','prdmsy','prdout')) )
CREATE PROCEDURE u_import_tritem
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or materialAS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and (@product = 'B' and (a.move_type in ('decin','bomrtn')) or @product = 'A' and (a.move_type in ('decout','mkout','prdmsy','prdout')) )select * into #tt2 from dbo.trade e
where e.typeflag = @productselect a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or materialAS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and case @product when 'B' then ',decin,bomrtn,' when 'A' then ',decout,mkout,prdmsy,prdout,' end like '%,'+a.move_type+',%'select * into #tt2 from dbo.trade e
where e.typeflag = @productselect a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or materialASif @product='A'
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no
where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and a.move_type in ('decout','mkout','prdmsy','prdout') if @product='b'
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no
where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and a.move_type in ('decin' ,'bomrtn')select * into #tt2 from dbo.trade e
where e.typeflag = @productselect a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code
分开写,简单明了
大力兄,是不是再检查一下你的方法?
when 'B' then 'decin' or'bomrtn'
when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout'
end ) 好像不能给一个变量两个直吧!
另外mkout' 应该为'mkout' 吧!不知道能不能给你点帮助