Joining two queries in one query

[AIMAM]

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:

Code: Select all

(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

The second query is:

(

Code: Select all

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?

Please help...

[AIMAM]

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

[admin]

topic.asp?TOPIC_ID=4211