output with total item = 0
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: output with total item = 0

  1. #1
    Join Date
    May 2008
    Posts
    2

    output with total item = 0

    Hi friends,
    really appreciate if anyone could help me on below queries

    SELECT nvl(sum(Prl.quantity),0) Quantity, msi.segment1 item
    FROM
    po_requisition_lines_ALL PRL,
    mtl_system_items_b msi,
    invfg_onhand_balances iob
    where
    msi.inventory_item_id = iob.inventory_item_id (+) and
    msi.organization_id = iob.organization_id (+) and
    msi.inventory_item_id = prl.item_id(+)
    and msi.organization_id = prl.destination_organization_id(+)
    and msi.segment1 like 'Z%'
    group by msi.segment1
    order by msi.segment1


    i only need the output for item with sum(Prl.quantity) = 0
    currently the output will list all the items even total > 0.
    thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    and sum(prl.quantity) = 0

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,440

    Talking

    Try this:

    Code:
    Select SUM(NVL(Prl.Quantity,0)) Quantity, Msi.Segment1 Item
      From Po_Requisition_Lines_All Prl
         , Mtl_System_Items_B Msi
         , Invfg_Onhand_Balances Iob
     Where
           Msi.Inventory_Item_Id = Iob.Inventory_Item_Id (+) 
       And Msi.Organization_Id = Iob.Organization_Id (+) 
       And Msi.Inventory_Item_Id = Prl.Item_Id(+) 
       And Msi.Organization_Id = Prl.Destination_Organization_Id(+)
       And Msi.Segment1 Like 'Z%'
     Group By Msi.Segment1
    Having Sum(Nvl(Prl.Quantity,0)) = 0
     Order By Msi.Segment1
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width