orcale视图的问题，实在解决不了！请求帮忙

[tangyun99141]

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）相减


请高手帮帮忙哦

[Visual_Studio_Net]

两个表练个关联字段都没有，怎么确定order.trans='buy'所有记录OrderALLOC.exec_qty的和？？？？？？

[henghowzc]

两个表你关联没有说阿

[tangyun99141]

不好意思， 两个表用 order_id 关联，order_id 这个字段两个表里都有的

[Visual_Studio_Net]

看这个行不行，奇怪了，楼主你用order做表名，建表的时候不报错？？？
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

[renmao]

看这个行不行
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

[llkyq]

create or replace view v_test
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