集思广益

Question

create or replace view vv_om110_delivery_v as
select  
    de.delivery_id,   --调拨单号     
    wdd.inventory_item_id,  --产品id
    sum(wdd.requested_quantity) quantity,  --定购数量
    ic.segment3            --产品类别
from 
    wsh_new_deliveries                       de , 
    wsh_delivery_details                     wdd,
    wsh_delivery_assignments                 wda,
    mtl_item_categories_v                    ic
where
     wdd.delivery_detail_id    = wda.delivery_detail_id
and  wda.delivery_id           = de.delivery_id
and  ic.organization_id        = wdd.organization_id
and  ic.inventory_item_id      = wdd.inventory_item_idgroup by de.delivery_id,wdd.inventory_item_id
order by de.delivery_id;

Answer 1

谢了，ic.segment3我在查询中还需要，楼上的写的也不正确亚，还有为什么把中间一个select集合给去掉了了呢，我是加了distinct的，里面有好多重复的数据亚

Answer 2

如果产品类别主键定义恰当的话，稍改一下（segment3->segment1):
create or replace view vv_om110_delivery_v as
select  
    de.delivery_id,   --调拨单号     
    wdd.inventory_item_id,  --产品id
    sum(wdd.requested_quantity) quantity,  --定购数量
    ic.segment1            --产品类别
from 
    wsh_new_deliveries                       de , 
    wsh_delivery_details                     wdd,
    wsh_delivery_assignments                 wda,
    mtl_item_categories_v                    ic
where
     wdd.delivery_detail_id    = wda.delivery_detail_id
and  wda.delivery_id           = de.delivery_id
and  ic.organization_id        = wdd.organization_id
and  ic.inventory_item_id      = wdd.inventory_item_idgroup by de.delivery_id,wdd.inventory_item_id
order by de.delivery_id;

Answer 3

还是不正确，ic.segment1没有在group up中出现吧

Answer 4

还有亚，加不加以下可以吗，为什么呀？
    (
      Select Distinct 
             organization_id,
             inventory_item_id,
             segment1 segment3
      From mtl_item_categories_v
    )                                        ic

Answer 5

有必要加group by ic.segment1吗？
不是对它分组统计啊。对于ic的distinct，好象是多余的,因为后面的条件已作判断。

Answer 6

不加的话出现错误亚，不是说select中的字段都应该在group by中出现马

Answer 7

"不是说select中的字段都应该在group by中出现马",
应反过来说,group by字段应在select中出现 
 :)