I have two queries from the table "mtl_onhand_quantities", that i need to represnt in the same report. Each query represents the same column but with different conditions.
(select INVENTORY_ITEM_ID,sum (TRANSACTION_QUANTITY) as "MAINWAREHOUSE QTY"
from mtl_onhand_quantities
where
ORGANIZATION_ID= 209
AND SUBINVENTORY_CODE='MWarehouse'
OR SUBINVENTORY_CODE = 'MWAREHOUSE'
Group by
INVENTORY_ITEM_ID) -- 30 record
(select INVENTORY_ITEM_ID,sum (TRANSACTION_QUANTITY) as "Business Centres QTY "
from mtl_onhand_quantities
where
ORGANIZATION_ID= 209
AND SUBINVENTORY_CODE <>'MWarehouse'
AND SUBINVENTORY_CODE <> 'MWAREHOUSE'
Group by
INVENTORY_ITEM_ID) -- 20 record
As you can see the number of records of each query is different from the other.
How can i join the two columns in one query, for reporting purposes?
I think you can put union function. you can get the results both of 2 queries to be at the same report like that:
(select INVENTORY_ITEM_ID,sum (TRANSACTION_QUANTITY) as "MAINWAREHOUSE QTY"
from mtl_onhand_quantities
where
ORGANIZATION_ID= 209
AND SUBINVENTORY_CODE='MWarehouse'
OR SUBINVENTORY_CODE = 'MWAREHOUSE'
Group by
INVENTORY_ITEM_ID)
union
(select INVENTORY_ITEM_ID,sum (TRANSACTION_QUANTITY) as "Business Centres QTY "
from mtl_onhand_quantities
where
ORGANIZATION_ID= 209
AND SUBINVENTORY_CODE <>'MWarehouse'
AND SUBINVENTORY_CODE <> 'MWAREHOUSE'
Group by
INVENTORY_ITEM_ID)
select INVENTORY_ITEM_ID
,sum(DECODE(UPPER(SUBINVENTORY_CODE),'MWAREHOUSE',TRANSACTION_QUANTITY)) as "MAINWAREHOUSE QTY"
,sum(DECODE(UPPER(SUBINVENTORY_CODE),'MWAREHOUSE',NULL, TRANSACTION_QUANTITY)) as "Business Centres QTY"
from mtl_onhand_quantities
where ORGANIZATION_ID= 209
Group by INVENTORY_ITEM_ID
you can find the full thread in the following url:
Now I do have the following two queries that i would like to join together.
select INVENTORY_ITEM_ID,
sum(CASE WHEN UPPER(SUBINVENTORY_CODE) = 'MWAREHOUSE'
THEN TRANSACTION_QUANTITY
ELSE Null END) as "MAINWAREHOUSE QTY",
sum(CASE WHEN UPPER(SUBINVENTORY_CODE) <> 'MWAREHOUSE'
THEN TRANSACTION_QUANTITY
ELSE Null END) as "Business Centres QTY "
from mtl_onhand_quantities
where ORGANIZATION_ID = 209 Group by INVENTORY_ITEM_ID
select
RSH.ITEM_ID,RSH.ITEM_DESCRIPTION
,sum (Pll.QUANTITY) As "QTY Ordered"
,sum(Pll.QUANTITY_RECEIVED) AS "QTY Received" ,
from
PO_LINE_LOCATIONS_ALL PLL
, RCV_SHIPMENT_LINES RSH
where
PLL.LINE_LOCATION_ID=RSH.PO_LINE_LOCATION_ID
AND RSH.ITEM_ID is not NULL
Group by
RSH.ITEM_ID,RSH.ITEM_DESCRIPTION
