本人現在做一份報表使用group left的形試
select mtl_item_quantities_view.subinventory_code sub_inv,substr(T.cost_category,1,2) category,
mtl_system_items_b.segment1 itemno,mtl_system_items_b.description description,
mtl_item_quantities_view.quantity onhand, CST_ITEM_COST_TYPE_V.item_cost,
(mtl_item_quantities_view.quantity)*( CST_ITEM_COST_TYPE_V.item_cost) Amount
from mtl_system_items_b,mtl_item_quantities_view,
(select mtl_item_categories_v.inventory_item_id,
mtl_item_categories_v.ORGANIZATION_ID,
mtl_item_categories_v.category_concat_segs cost_category
from mtl_item_categories_v
wheremtl_item_categories_v.CATEGORY_SET_NAME='Cost') T,
CST_ITEM_COST_TYPE_V
where cost_type='Frozen'
and mtl_item_quantities_view.organization_id=mtl_system_items_b.organization_id
and mtl_item_quantities_view.inventory_item_id=mtl_system_items_b.inventory_item_id
and T.inventory_item_id(+)=mtl_item_quantities_view.inventory_item_id
and T.organization_id(+)=mtl_item_quantities_view.organization_id
and CST_ITEM_COST_TYPE_V.inventory_item_id(+)=mtl_item_quantities_view.inventory_item_id
and CST_ITEM_COST_TYPE_V.organization_id(+)=mtl_item_quantities_view.organization_id
order by substr(T.cost_category,1,2),mtl_item_quantities_view.subinventory_code
其中group left by category 和subinventory_code
一個category下有幾個不同的subinventory_code通過group left可以使
同一個category下的同一個subinventory_code的amount加總
現在求同一個category的amount加總
並且只出現在同一個category的最後即同一個category的amount加總
只出現一次
以下是我寫的cf(不知是否需要)
function CF_1Formula return Number is
v_sum number;
begin
select sum((mtl_item_quantities_view.quantity)*( CST_ITEM_COST_TYPE_V.item_cost)) into v_sum
from mtl_item_quantities_view,CST_ITEM_COST_TYPE_V,(select mtl_item_categories_v.inventory_item_id,
mtl_item_categories_v.ORGANIZATION_ID,
mtl_item_categories_v.category_concat_segs cost_category
from mtl_item_categories_v
wheremtl_item_categories_v.CATEGORY_SET_NAME='Cost') T
wherecost_type='Frozen'
and substr(T.cost_category,1,2)=:category
and CST_ITEM_COST_TYPE_V.inventory_item_id(+)=mtl_item_quantities_view.inventory_item_id
and CST_ITEM_COST_TYPE_V.organization_id(+)=mtl_item_quantities_view.organization_id
and T.inventory_item_id(+)=mtl_item_quantities_view.inventory_item_id
and T.organization_id(+)=mtl_item_quantities_view.organization_id
group by substr(T.cost_category,1,2);
return(v_sum);
exception when others then
return(null);
end;
|
