ERPstuff - Oracle EBS Community

Hello,

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.

The first query:
The second query is:
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?

Please help...

Hi AIMAM

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)

hope it work fine.

Thanks
Hisham

Thank you..

I found the following and it worked perfectly:

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:

http://forums.oracle.com/forums/thread. ... dID=674252

Hi AIMAM

Happy for the solving your issue.

Thanks
Hisham

hello,

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

Always appreciate your help...

For the answer I have followed please folow the link:

http://forums.oracle.com/forums/thread. ... 2&tstart=1