Home » SQL & PL/SQL » SQL & PL/SQL » Joining two queries in one query
Joining two queries in one query [message #329357] Wed, 25 June 2008 03:26 Go to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
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:

(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:

(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...

[Updated on: Wed, 25 June 2008 03:28]

Report message to a moderator

Re: Joining two queries in one query [message #329358 is a reply to message #329357] Wed, 25 June 2008 03:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You will have to move the logic in the where clause to your sum.
In CASE SUBINVENTORY_CODE in ('MWarehouse', 'MWAREHOUSE'), then you sum transaction_quantity, ELSE zero for the mainwarehouse_qty column.
The other way around for the second summation
Re: Joining two queries in one query [message #329367 is a reply to message #329357] Wed, 25 June 2008 04:03 Go to previous messageGo to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
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

Re: Joining two queries in one query [message #329372 is a reply to message #329367] Wed, 25 June 2008 04:18 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I found the following
Of course, you mean you were given the following . Note** It is always good to reference those who have soved your issue for you (even/especially if from a different forum)

[Updated on: Wed, 25 June 2008 04:18]

Report message to a moderator

Re: Joining two queries in one query [message #329375 is a reply to message #329367] Wed, 25 June 2008 04:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Abeer30 wrote on Wed, 25 June 2008 11:03
Thank you..

I found the following and it worked perfectly:

From your queries, it was not clear that you could safely use upper on the subinventory_code..
Re: Joining two queries in one query [message #330314 is a reply to message #329372] Sun, 29 June 2008 06:45 Go to previous messageGo to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
I do appreciate the help in all the forums that I'm member in.

Not referencing from where i got the support, was made by mistake, as a kind of respect to this forum.

As I was wrong I do thank you for your note.

[Updated on: Sun, 29 June 2008 07:03]

Report message to a moderator

Re: Joining two queries in one query [message #330316 is a reply to message #330314] Sun, 29 June 2008 07:05 Go to previous messageGo to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
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...
Re: Joining two queries in one query [message #330764 is a reply to message #330316] Tue, 01 July 2008 02:45 Go to previous message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
For the answer I have followed please folow the link:

http://forums.oracle.com/forums/thread.jspa?threadID=674252&tstart=1
Previous Topic: convert from MM-DD-YYYY to DD-MM-YYYY
Next Topic: problem with query
Goto Forum:
  


Current Time: Mon Dec 05 08:36:46 CST 2016

Total time taken to generate the page: 0.08205 seconds