USER的要求 抓出2010年某些客戶的銷貨數量與金額,及各ITEM的PO筆數.[@more@]create table a1 as select ac.customer_id,ac.customer_number,ac.CUSTOMER_NAME, o.organization_id,o.ORGANIZATION_CODE,o.name org_name, h.TRANSACTIONAL_CURR_CODE curr_code,l.inventory_item_id, m.segment1 item, sum(l.shipped_quantity) qty, sum(l.shipped_quantity*nvl(l.UNIT_SELLING_price,0)) amount from oe_order_headers_all h, oe_order_lines_all l, ar_customers ac, mtl_system_items_b m, oe_ship_from_orgs_v o where ac.customer_id=h.sold_to_org_id and ac.customer_number in ('5934','1038','1059','7330','17687','9094','7850','5934') and l.header_id=h.header_id and m.inventory_item_id=l.inventory_item_id and m.organization_id=1 and l.shipped_quantity>0 and to_char(l.ACTUAL_SHIPMENT_DATE,'yyyy') ='2010' and o.organization_id=h.org_id group by ac.customer_id,l.inventory_item_id,ac.customer_number,ac.CUSTOMER_NAME, o.organization_id,o.ORGANIZATION_CODE,o.name , h.TRANSACTIONAL_CURR_CODE , m.segment1 CREATE OR REPLACE function aa0aa (p_custid in number,p_itemid in number,p_orgid in number) return number is v_num number; begin select count(1) into v_num from (select distinct oh.cust_po_number from oe_order_headers_all oh, oe_order_lines_all ol where oh.sold_to_org_id=p_custid and ol.inventory_item_id=p_itemid and to_char(ol.ACTUAL_SHIPMENT_DATE,'yyyy')=2010 and oh.org_id=p_orgid and ol.header_id=oh.header_id ); return nvl(v_num,0); end; create table a2 as select a1.*, aa0aa(a1.customer_id,a1.inventory_item_id,a1.organization_id) po_num from a1 select * from a2;