想不通,弄了一个下午,存储过程没如何问题
只是我在前面加了个条件没有起到作用,存储过程可以运行,在倒首第三行加了个条件
我想判断 WH.OK_qty+WI.sl-L.Goods_sl>0,语句代码是where isnull(WH.OK_qty,0)+isnull(wi.sl,0)-L.goods_sl>0goods_sl sl OK_QTY 其他字段我就不写了
10004.00 10004.00 .00
20008.00 20008.00 .00
.
.
这几个值加减应该都是零啊,为什么还会出现啊!!!!!!
CREATE Procedure test1
@OrderId varchar(8)
AS
Select L.P_id, Pro.P_name, Pro.P_spc,
L.Goods_sl, isnull(R.sl,0) as sl,
isnull(WI.sl,0) as wi_sl,
isnull(WH.OK_qty,0) as Ok_qty,
isnull(WH.NG_qty,0) as NG_qty,
L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0) as QL
From (
--查生产BOM需求-----------------------------------------
select P_id, Goods_sl from D_dd3
where ph_id in
(
select ph_id from D_dd2
where iid like @OrderId
)
--查生产BOM需求-----------------------------------------
) L
left outer join ( --查订单实领-----------------
-----------------------------
select p_id, sum(sl) as sl
from
(
--/退料
Select P_id, sum(-sl) as sl from I_uu2
where iid in
(
select iid from I_uu1
where kf_name like @OrderId
and cc = 'G'
)
group by P_id
--/退料
union
--/领用
select p_id, sum(sl) as sl from I_uu2
where iid in
(
select iid from I_uu1
where KF_Name = @OrderId
and cc = 'H'
)
group by p_id
--/领用
) A
group by p_id --查订单实领-----------------
) R
on L.p_id = R.p_id left outer join ( ---库存-----------------------------------------------------------
select A.p_id,
sum( Case when B.invid =0 Then B.inv_qty Else 0 End ) as Ok_qty,
sum( Case when B.invid =1 Then B.inv_qty Else 0 End ) as NG_qty
from D_dd3 A
inner join P_inv B
on A.p_no = B.p_no
where A.ph_id in
(
select ph_id from D_dd2
where iid like @OrderId
)
and B.inv_qty >0
Group by A.p_id ---库存-----------------------------------------------------------
) WH on L.p_id = WH.P_id left outer join (
---------待检------------------------------------------------------
select B.p_id, sum(B.sl) as sl from I_rr1 A
inner join I_rr2 B
on A.iid = B.iid
where A.status_id = 1
group by b.p_id
---------待检------------------------------------------------------
) WI on L.p_id = WI.P_id inner join A_Pro Pro
on L.p_id = Pro.P_id Order by 9 desc GO
只是我在前面加了个条件没有起到作用,存储过程可以运行,在倒首第三行加了个条件
我想判断 WH.OK_qty+WI.sl-L.Goods_sl>0,语句代码是where isnull(WH.OK_qty,0)+isnull(wi.sl,0)-L.goods_sl>0goods_sl sl OK_QTY 其他字段我就不写了
10004.00 10004.00 .00
20008.00 20008.00 .00
.
.
这几个值加减应该都是零啊,为什么还会出现啊!!!!!!
CREATE Procedure test1
@OrderId varchar(8)
AS
Select L.P_id, Pro.P_name, Pro.P_spc,
L.Goods_sl, isnull(R.sl,0) as sl,
isnull(WI.sl,0) as wi_sl,
isnull(WH.OK_qty,0) as Ok_qty,
isnull(WH.NG_qty,0) as NG_qty,
L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0) as QL
From (
--查生产BOM需求-----------------------------------------
select P_id, Goods_sl from D_dd3
where ph_id in
(
select ph_id from D_dd2
where iid like @OrderId
)
--查生产BOM需求-----------------------------------------
) L
left outer join ( --查订单实领-----------------
-----------------------------
select p_id, sum(sl) as sl
from
(
--/退料
Select P_id, sum(-sl) as sl from I_uu2
where iid in
(
select iid from I_uu1
where kf_name like @OrderId
and cc = 'G'
)
group by P_id
--/退料
union
--/领用
select p_id, sum(sl) as sl from I_uu2
where iid in
(
select iid from I_uu1
where KF_Name = @OrderId
and cc = 'H'
)
group by p_id
--/领用
) A
group by p_id --查订单实领-----------------
) R
on L.p_id = R.p_id left outer join ( ---库存-----------------------------------------------------------
select A.p_id,
sum( Case when B.invid =0 Then B.inv_qty Else 0 End ) as Ok_qty,
sum( Case when B.invid =1 Then B.inv_qty Else 0 End ) as NG_qty
from D_dd3 A
inner join P_inv B
on A.p_no = B.p_no
where A.ph_id in
(
select ph_id from D_dd2
where iid like @OrderId
)
and B.inv_qty >0
Group by A.p_id ---库存-----------------------------------------------------------
) WH on L.p_id = WH.P_id left outer join (
---------待检------------------------------------------------------
select B.p_id, sum(B.sl) as sl from I_rr1 A
inner join I_rr2 B
on A.iid = B.iid
where A.status_id = 1
group by b.p_id
---------待检------------------------------------------------------
) WI on L.p_id = WI.P_id inner join A_Pro Pro
on L.p_id = Pro.P_id Order by 9 desc GO
解决方案 »
- sql server2000 数据出错!
- 有关数据绑定的一个小问题,高手指点
- 大力:请帮解决这个问题:instead of update 触发器不能执行以下语句
- 这个“分层”的SQL如何写?
- 聚集索引和非聚集索引的区别??
- 欢迎讨论!大家都有什么好方法?关于图片处理!
- 为什么where setDate between '2005-09-26' and '2005-09-26'不显示当天记录?
- 为什么我不能向数据库插入数据?
- 在什么时候建立索引? 以及相关问题!!
- 一个简单的多表连接问题,大神??
- 一个简单的报表,帮忙看看如何最少代码实现?
- java连接SQLServer2005问题(急待解决)
问题是LZ并没有在脚本中加上
where (L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0)) > 0这是一种
还可以把整个select语句封装在子查询里, 再其外围用 where QL > 0就行了CREATE Procedure test1
@OrderId varchar(8)
AS
-- Tom加的 --begin
select *
from
(
-- Tom加的 -- end
Select L.P_id, Pro.P_name, Pro.P_spc,
L.Goods_sl, isnull(R.sl,0) as sl,
isnull(WI.sl,0) as wi_sl,
isnull(WH.OK_qty,0) as Ok_qty,
isnull(WH.NG_qty,0) as NG_qty,
L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0) as QL
From
(
--查生产BOM需求-----------------------------------------
select P_id, Goods_sl from D_dd3
where ph_id in
(
select ph_id from D_dd2
where iid like @OrderId
)
--查生产BOM需求-----------------------------------------
) L
left outer join
(
--查订单实领-----------------
-----------------------------
select p_id, sum(sl) as sl
from
(
--/退料
Select P_id, sum(-sl) as sl from I_uu2
where iid in
(
select iid from I_uu1
where kf_name like @OrderId
and cc = 'G'
)
group by P_id
--/退料
union
--/领用
select p_id, sum(sl) as sl from I_uu2
where iid in
(
select iid from I_uu1
where KF_Name = @OrderId
and cc = 'H'
)
group by p_id
--/领用
) A
group by p_id
--查订单实领-----------------
) R
on L.p_id = R.p_id
left outer join
(
---库存-----------------------------------------------------------
select A.p_id,
sum( Case when B.invid =0 Then B.inv_qty Else 0 End ) as Ok_qty,
sum( Case when B.invid =1 Then B.inv_qty Else 0 End ) as NG_qty
from D_dd3 A
inner join P_inv B
on A.p_no = B.p_no
where A.ph_id in
(
select ph_id from D_dd2
where iid like @OrderId
)
and B.inv_qty >0
Group by A.p_id
---库存-----------------------------------------------------------
) WH
on L.p_id = WH.P_id
left outer join
(
---------待检------------------------------------------------------
select B.p_id, sum(B.sl) as sl from I_rr1 A
inner join I_rr2 B
on A.iid = B.iid
where A.status_id = 1
group by b.p_id
---------待检------------------------------------------------------
) WI
on L.p_id = WI.P_id
inner join A_Pro Pro
on L.p_id = Pro.P_id
-- Tom加的 --begin
) as v
where QL > 0
-- Tom加的 --end
Order by 9 desc GO
不明白我为什么还要在后面加where (L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0)) > 0我不需要它满足我的 条件啊 ?请赐教
写的简单点
就是 我 执行 存储过程,得到的值类似为(其他字段我就不写了)
goods_sl sl OK_QTY 10004.00 10004.00 .00
20008.00 20008.00 .00
.
。
现在我只想查询where sl+OK_QTY-goods_sl<0所以我在倒首第三行加了where isnull(WH.OK_qty,0)+isnull(wi.sl,0)-L.goods_sl>0
为什么上面几个值不满足我的 条件(都为0),还是被查询出来了 啊 ,难道10004+.0.00-10004.00不等于零吗?实在想不出原因 啊
goods_sl sl OK_QTY
10004 10004 0.0
20008 20008 0.0
CREATE TABLE TBTEST(goods_sl INT, sl INT, OK_QTY INT)
INSERT TBTEST
SELECT 10004 , 10004, 0.0 UNION ALL
SELECT 20008 , 20008 , 0.0SELECT * FROM TBTEST WHERE ISNULL(goods_sl,0)-ISNULL(sl,0)-ISNULL(OK_QTY,0)>0
goods_sl sl OK_QTY
----------- ----------- ----------- (所影响的行数为 0 行)
我这样都没问题!
要不然你先把那些空字段设为ISNULL('',0)
然后在最后不用ISNULL试试?
而且那些字段是INT?
因为要看OK_qty, sl的类型了, 如果是浮点型, 真的不为零了
对于浮点型, 可以拿一个接近0的数来比较, 如0.000000000001
把
where isnull(WH.OK_qty,0)+isnull(wi.sl,0)-L.goods_sl>0
修改成
where isnull(WH.OK_qty,0)+isnull(wi.sl,0)-L.goods_sl>0.000000000001