Home » SQL & PL/SQL » SQL & PL/SQL » subinventories that have no locators
subinventories that have no locators [message #249099] Tue, 03 July 2007 06:50 Go to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
hello,

I have to develop an inventory report for oracle apps:

the query i have:

select
mwb.organization_id, mwb.organization_code,
mwb.subinventory_code,
mwb.locator_id, mwb.locator,
mwb.inventory_item_id, mwb.item, mwb.item_description,
mwb.uom, mwb.on_hand
,cic.item_cost
from MTL_ONHAND_TOTAL_MWB_V mwb
,cst_item_cost_type_v cic
,mtl_item_locations_kfv il
where 1=1
and cic.inventory_item_id = mwb.inventory_item_id
and cic.organization_id = mwb.organization_id
and cic.COST_TYPE = 'Average'
and il.inventory_location_id = mwb.locator_id
and mwb.SUBINVENTORY_CODE = 'xxx';

my problem is that there are some subinventories that have no locators and the query would only work if the line :
and il.inventory_location_id = mwb.locator_id
was not there

what shall i do to include both options in my query?
Re: subinventories that have no locators [message #249102 is a reply to message #249099] Tue, 03 July 2007 06:54 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Try an Outer Join.

I'm not familiar with Oracle Inventory, I'm not sure which way the outer join should go.

Re: subinventories that have no locators [message #249106 is a reply to message #249102] Tue, 03 July 2007 07:14 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Quote:
il.inventory_location_id = mwb.locator_id


Does this hold good for your requirement?
il.inventory_location_id = nvl(mwb.locator_id,'No Locator for the inventory')
Re: subinventories that have no locators [message #249111 is a reply to message #249102] Tue, 03 July 2007 07:33 Go to previous message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
hello,

thanks a lot.

yes it worked with an outer join

thanks again... Smile
Previous Topic: forall for performance
Next Topic: Get unique rows from table
Goto Forum:
  


Current Time: Sun Dec 04 12:51:02 CST 2016

Total time taken to generate the page: 0.14588 seconds