多表连接查询

select ol.item_id,
       ii.item_code,
       ii.description,
       ol.uom,
       sum(ol.order_quantity - nvl(ol.delivery_quantity, 0)) as require_quantity, --订单需求
       nvl((select sum(a.primary_quantity)
             from inv_quantities_onhand a
            where a.storage_location_id in
                  (select x.storage_location_id
                     from inv_storage_locations_vl x
                    where x.storage_attribute_code = 'A')
              and a.item_id = ol.item_id
            group by a.item_id),
           0) as onhand_quantity, --有效在库
       nvl((select sum(a.primary_quantity)
             from inv_quantities_onhand a
            where a.storage_location_id in
                  (select x.storage_location_id
                     from inv_storage_locations_vl x
                    where x.storage_location_type = '2')
              and a.item_id = ol.item_id
            group by a.item_id),
           0) as consign_quantity, --寄售在库
       nvl((select sum(a.transaction_quantity) --转移在途     
             from ainv_transfer_rq_lines a
            where a.item_id = ol.item_id
            group by a.item_id),
           0) as trans_quantity,
       nvl((select sum(a.primary_quantity - nvl(a.received_quantity, 0)) --预计到货          
             from pur_purchase_order_lines a
            where a.purchasing_item_id = ol.item_id ---
           -- and a.required_date<:select.end_date
            group by (a.purchasing_item_id)),
           0) as expect_quantity
  from ord_sales_order_headers        oh,
       ord_sales_order_lines          ol,
       inv_sys_items_basics_vl        ii,
       ord_sales_organizations_vl     os,
       inv_inventory_organizations_vl iiv
 where oh.sales_order_id = ol.sales_order_id
   and ii.item_id = ol.item_id
   and oh.sales_organization_id = os.SALES_ORGANIZATION_ID
   and oh.default_delivery_inv_org_id = iiv.INVENTORY_ORGANIZATION_id
 group by ol.item_id, ii.item_code, ii.description, ol.uom;