存储过程执行正常,我 现在 想 加一个条件,为什么 我在倒首第三行加了一个where QL>0 提示列名QL无效,明明执行test1得到了QL的值啊 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_idleft 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---库存-----------------------------------------------------------
) WHon L.p_id = WH.P_idleft 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
---------待检------------------------------------------------------
) WIon L.p_id = WI.P_idinner join A_Pro Pro
on L.p_id = Pro.P_idOrder by 9 descGO
@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_idleft 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---库存-----------------------------------------------------------
) WHon L.p_id = WH.P_idleft 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
---------待检------------------------------------------------------
) WIon L.p_id = WI.P_idinner join A_Pro Pro
on L.p_id = Pro.P_idOrder by 9 descGO
@OrderId varchar(8)
AS
select *
from
(
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
) t
where QL>0 --在这里加吗?
Order by 9 desc GO
你还是这么写吧:
where L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0)>0,上面的QL只是作为别名出现,不能直接用在
where 条件中,除非你再嵌套一下。
2、where子句基于指定的条件对记录行进行筛选;
3、group by子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用having子句筛选分组;
6、计算所有的表达式;
7、使用order by对结果集进行排序。
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
你引用QL时别名还没创建, 加: L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0)>0 就对了
-- 把
where QL > 0
-- 修改成
where (L.Goods_sl - isnull(R.sl,0) - isnull(WH.OK_qty,0)) > 0