Order表里有 如下几个字段:order.trans, order.status, order.orderId, order.OrderName
OrderALLOC表里有如下子段: OrderALLOC.exec_qty
想建的视图有如下列 OrderID, OrderName, 数量
数量的设法为 order.status='ready' 时
order.trans='buy'的所有记录的OrderALLOC.exec_qty字段的和(SUM)与
order.trans='sell'的所有记录的OrderALLOC.exec_qty字段的和(SUM)相减
请高手帮帮忙哦
create or replace view v_test
as
select a.orderid,
a.ordername,
case a.status when 'ready' then
(
select (c.qty-d.qyt) from
(select sum(orderalloc.exec_qty) qty from orderalloc,order where order.trans='buy' and order.id=orderalloc.id) c,
(select sum(orderalloc.exec_qty) qty from orderalloc,order where order.trans='sell' and order.id=orderalloc.id) d
)
end as order_test
from order a,orderalloc b
where a.orderid=b.orderid
select order.OrderID,order.OrderName
case order.status when 'ready' then
( select sum(t.c_number) from
(
select sum(rderALLOC.exec_qty) as c_number
from OrderALLOC c,order o
where c.order_id=o.order_id and o.status='ready' and o.trans='sell'
union all
select -sum(rderALLOC.exec_qty) as c_number
from OrderALLOC c,order o
where c.order_id=o.order_id and o.status='ready' and o.trans='buy'
) t
)
else 0
from OrderALLOC c,order o
where c.order_id=o.order_id
as
select a.orderid,
a.ordername,
case a.status when 'ready' then
sum(decode(a.trans,'buy',1,'sell',-1)*b.exec_qty) order_test
end as order_test
from order a,orderalloc b
where a.orderid=b.orderid
group by a.orderid,
a.ordername