[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Adding results where transaction_quantity is null

Posted on 2009-02-16
4
Medium Priority
?
584 Views
Last Modified: 2013-12-07
I'm altering a report that show the quantity on stock owned by the supplier and the value of that stock. I would also like to show the products that have 0 (zero) quantity registreret on the supplier.  As I'm a novice in PL/SQL I'm not sure what would be the best approach to solve this. If my question is not detailed enough, just let me know and I will try to explain this in more detail.


select moh.inventory_item_id,
       moh.organization_id organization_id,
       sum(moh.transaction_quantity)
       ,(sum(moh.transaction_quantity) * pol.unit_price)
       ,moh.subinventory_code
       ,il.segment1
        
        ,il.description 
       ,moh.is_consigned
       ,pol.unit_price 
       ,poh.currency_code 
       ,v.vendor_name
       ,i.description
 from  apps.MTL_ONHAND_QUANTITIES_DETAIL moh, 
       apps.cst_item_costs ic, 
       apps.mtl_item_locations il, 
       apps.PO_HEADERS_ALL poh, 
       apps.PO_LINES_ALL pol, 
       apps.po_vendors v, 
       apps.po_vendor_sites_all vs,
       apps.mtl_system_items_b i 
 
where  moh.inventory_item_id = ic.inventory_item_id
      and moh.organization_id = ic.organization_id
      and moh.organization_id = il.organization_id(+)
      and moh.subinventory_code=il.subinventory_code(+)
      and moh.locator_id = il.inventory_location_id(+)
      and moh.is_consigned = '1'  
      and poh.po_header_id = pol.po_header_id 
      and pol.item_id=moh.inventory_item_id 
      and moh.owning_organization_id is not null
      and moh.owning_tp_type <> '2'
      and moh.owning_organization_id=vs.vendor_site_id 
      and v.vendor_id=vs.vendor_id 
  and poh.type_lookup_code like 'BLANKET%'
  and i.inventory_item_id(+) = moh.inventory_item_id
  and i.organization_id(+) =moh.organization_id
group by moh.inventory_item_id,
          moh.organization_id,
          moh.subinventory_code,
          ic.item_cost,
          il.segment1,
          il.description,
          moh.is_consigned,
          pol.unit_price, 
          poh.currency_code,
          v.vendor_name,
          i.description

Open in new window

0
Comment
Question by:gosi75
  • 2
4 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 23650808
What is the result of your query and what are you expecting?
0
 

Author Comment

by:gosi75
ID: 23651927
I get results where there is always existing some transaction_quantity, moh.transaction_quantity is the amount on stock owned by the supplier. I would like to join on the apps.mtl_system_items_b table so that I will get the total item list and add it to the result. That is, showing the transaction quantity when it
exists but also showing the rest of the items from the apps.mtl_system_items_b table.

I hope this helps, If not I will try to be more precise.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1500 total points
ID: 23652749
Use nvl(moh.transaction_quantity,0) in place of moh.transaction_quantity.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24028438
B? Did that not work?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question