select t1.Fbillno 单据号, t1.fdate 制单时间, '杨帆' 制单人, '2.99' 部门, '02.06.02' 业务员, kehu.f_108 仓库助记码, '转库' 收发类别, @thbzhi@ 退货标志, sp.disfnumber 厂商编码, sp.fname 存货名称, t2.fqty 数量, (case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价 from (SELECT * from icstockbill where fdate between ******** and ######## and ftrantype in(21,41) and fsupplyid is not null) t1 --根据退货标志的不同,取icstockbill中的相应的数据 left outer join icstockbillentry t2 on t1.finterid=t2.finterid left outer join t_organization kehu on t1.fsupplyid=kehu.fitemid left outer join Lch_icitem sp on t2.fitemid = sp.fitemid where kehu.fnumber between *CustNo* and #CustNo# and (case when @thbzhi@ = 0 then t1.Fheadselfd0138=40183 AND t1.frob=1 else t1.Fheadselfd0138=40184 and t1.frob=2 end)
改成如下 where (@thbzhi@ = 0 and t1.Fheadselfd0138=40183 AND t1.frob=1) or (t1.Fheadselfd0138=40184 and t1.frob=2)
前央回复的where不对,应该这样改where kehu.fnumber between *CustNo* and #CustNo# and (t1.Fheadselfd0138=case when @thbzhi@ = 0 then 40183 else 40184 end and t1.frob=case when @thbzhi@ = 0 then 1 else 2 end)
select t1.Fbillno 单据号, t1.fdate 制单时间, '杨帆' 制单人, '2.99' 部门, '02.06.02' 业务员, kehu.f_108 仓库助记码, '转库' 收发类别, @thbzhi@ 退货标志, sp.disfnumber 厂商编码, sp.fname 存货名称, t2.fqty 数量, (case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价 from (SELECT * from icstockbill where fdate between ******** and ######## and ftrantype in(21,41) and fsupplyid is not null and ((@thbzhi@ = 0 and t1.Fheadselfd0138=40183 AND t1.frob=1) or (t1.Fheadselfd0138=40184 and t1.frob=2))) t1 --根据退货标志的不同,取icstockbill中的相应的数据 left outer join icstockbillentry t2 on t1.finterid=t2.finterid left outer join t_organization kehu on t1.fsupplyid=kehu.fitemid left outer join Lch_icitem sp on t2.fitemid = sp.fitemid where kehu.fnumber between *CustNo* and #CustNo#
我想当@thbzhi@ = 0 时,t1.Fheadselfd0138=40183 AND t1.frob=1 反之,t1.Fheadselfd0138=40184 and t1.frob=2,应该怎样写?
from (SELECT * from icstockbill where fdate between ******** and ######## and ftrantype in(21,41) and fsupplyid is not null and ((@thbzhi@ = 0 and t1.Fheadselfd0138=40183 AND t1.frob=1) or (@thbzhi@ <> 0 and t1.Fheadselfd0138=40184 and t1.frob=2 end))) t1
不行的话就用下面的,一定OK select t1.Fbillno 单据号, t1.fdate 制单时间, '杨帆' 制单人, '2.99' 部门, '02.06.02' 业务员, kehu.f_108 仓库助记码, '转库' 收发类别, @thbzhi@ 退货标志, sp.disfnumber 厂商编码, sp.fname 存货名称, t2.fqty 数量, (case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价 from (SELECT * from icstockbill where fdate between ******** and ######## and ftrantype in(21,41) and fsupplyid is not null and t1.Fheadselfd0138=case when @thbzhi@ = 0 then 40183 else 40184 end and t1.frob=case when @thbzhi@ = 0 then 1 else 2 end--根据退货标志的不同,取icstockbill中的相应的数据 left outer join icstockbillentry t2 on t1.finterid=t2.finterid left outer join t_organization kehu on t1.fsupplyid=kehu.fitemid left outer join Lch_icitem sp on t2.fitemid = sp.fitemid where kehu.fnumber between *CustNo* and #CustNo#
不好意思,楼上少了“) t1” select t1.Fbillno 单据号, t1.fdate 制单时间, '杨帆' 制单人, '2.99' 部门, '02.06.02' 业务员, kehu.f_108 仓库助记码, '转库' 收发类别, @thbzhi@ 退货标志, sp.disfnumber 厂商编码, sp.fname 存货名称, t2.fqty 数量, (case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价 from (SELECT * from icstockbill where fdate between ******** and ######## and ftrantype in(21,41) and fsupplyid is not null and Fheadselfd0138=case when @thbzhi@ = 0 then 40183 else 40184 end and frob=case when @thbzhi@ = 0 then 1 else 2 end) t1--根据退货标志的不同,取icstockbill中的相应的数据 left outer join icstockbillentry t2 on t1.finterid=t2.finterid left outer join t_organization kehu on t1.fsupplyid=kehu.fitemid left outer join Lch_icitem sp on t2.fitemid = sp.fitemid where kehu.fnumber between *CustNo* and #CustNo#
(case when @thbzhi@ = 0 then t1.Fheadselfd0138=40183 AND t1.frob=1 else t1.Fheadselfd0138=40184 and t1.frob=2 end)) (case when [@thbzhi@] = 0 then t1.Fheadselfd0138=40183 AND t1.frob=1 else t1.Fheadselfd0138=40184 and t1.frob=2 end))
t1.Fbillno 单据号,
t1.fdate 制单时间,
'杨帆' 制单人,
'2.99' 部门,
'02.06.02' 业务员,
kehu.f_108 仓库助记码,
'转库' 收发类别,
@thbzhi@ 退货标志,
sp.disfnumber 厂商编码,
sp.fname 存货名称,
t2.fqty 数量,
(case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价
from
(SELECT * from icstockbill where fdate between ******** and ######## and ftrantype in(21,41) and fsupplyid is not null) t1 --根据退货标志的不同,取icstockbill中的相应的数据
left outer join icstockbillentry t2
on t1.finterid=t2.finterid
left outer join t_organization kehu
on t1.fsupplyid=kehu.fitemid
left outer join Lch_icitem sp
on t2.fitemid = sp.fitemid
where kehu.fnumber between *CustNo* and #CustNo#
and (case when @thbzhi@ = 0 then t1.Fheadselfd0138=40183 AND t1.frob=1 else t1.Fheadselfd0138=40184 and t1.frob=2 end)
where (@thbzhi@ = 0 and t1.Fheadselfd0138=40183 AND t1.frob=1) or (t1.Fheadselfd0138=40184 and t1.frob=2)
and (t1.Fheadselfd0138=case when @thbzhi@ = 0 then 40183 else 40184 end and t1.frob=case when @thbzhi@ = 0 then 1 else 2 end)
t1.Fbillno 单据号,
t1.fdate 制单时间,
'杨帆' 制单人,
'2.99' 部门,
'02.06.02' 业务员,
kehu.f_108 仓库助记码,
'转库' 收发类别,
@thbzhi@ 退货标志,
sp.disfnumber 厂商编码,
sp.fname 存货名称,
t2.fqty 数量,
(case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价
from
(SELECT * from icstockbill
where fdate between ******** and ########
and ftrantype in(21,41)
and fsupplyid is not null
and ((@thbzhi@ = 0 and t1.Fheadselfd0138=40183 AND t1.frob=1) or (t1.Fheadselfd0138=40184 and t1.frob=2))) t1
--根据退货标志的不同,取icstockbill中的相应的数据
left outer join icstockbillentry t2
on t1.finterid=t2.finterid
left outer join t_organization kehu
on t1.fsupplyid=kehu.fitemid
left outer join Lch_icitem sp
on t2.fitemid = sp.fitemid
where kehu.fnumber between *CustNo* and #CustNo#
(SELECT *
from icstockbill
where fdate between ******** and ########
and ftrantype in(21,41)
and fsupplyid is not null
and ((@thbzhi@ = 0 and t1.Fheadselfd0138=40183 AND t1.frob=1) or
(@thbzhi@ <> 0 and t1.Fheadselfd0138=40184 and t1.frob=2 end))) t1
select
t1.Fbillno 单据号,
t1.fdate 制单时间,
'杨帆' 制单人,
'2.99' 部门,
'02.06.02' 业务员,
kehu.f_108 仓库助记码,
'转库' 收发类别,
@thbzhi@ 退货标志,
sp.disfnumber 厂商编码,
sp.fname 存货名称,
t2.fqty 数量,
(case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价
from
(SELECT * from icstockbill
where fdate between ******** and ########
and ftrantype in(21,41)
and fsupplyid is not null and t1.Fheadselfd0138=case when @thbzhi@ = 0 then 40183 else 40184 end
and t1.frob=case when @thbzhi@ = 0 then 1 else 2 end--根据退货标志的不同,取icstockbill中的相应的数据
left outer join icstockbillentry t2
on t1.finterid=t2.finterid
left outer join t_organization kehu
on t1.fsupplyid=kehu.fitemid
left outer join Lch_icitem sp
on t2.fitemid = sp.fitemid
where kehu.fnumber between *CustNo* and #CustNo#
select
t1.Fbillno 单据号,
t1.fdate 制单时间,
'杨帆' 制单人,
'2.99' 部门,
'02.06.02' 业务员,
kehu.f_108 仓库助记码,
'转库' 收发类别,
@thbzhi@ 退货标志,
sp.disfnumber 厂商编码,
sp.fname 存货名称,
t2.fqty 数量,
(case when T2.fentryselfb0157 is not null then T2.fentryselfb0157 else t2.fentryselfd0146 end) 建议零售价
from
(SELECT * from icstockbill
where fdate between ******** and ########
and ftrantype in(21,41)
and fsupplyid is not null and Fheadselfd0138=case when @thbzhi@ = 0 then 40183 else 40184 end
and frob=case when @thbzhi@ = 0 then 1 else 2 end) t1--根据退货标志的不同,取icstockbill中的相应的数据
left outer join icstockbillentry t2
on t1.finterid=t2.finterid
left outer join t_organization kehu
on t1.fsupplyid=kehu.fitemid
left outer join Lch_icitem sp
on t2.fitemid = sp.fitemid
where kehu.fnumber between *CustNo* and #CustNo#