SQL Server Performance

Where to apply filters

Discussion in 'SQL Server 2008 General Developer Questions' started by alisag, Jan 27, 2010.

  1. alisag New Member

    I have the following query
    SELECT Item.No_, Item.Description, Table1.Inventory, Table2.[Qty on Sales Order], Table3.[Qty on Blanket Order]
    FROM [CompanyName$Item] AS Item LEFT OUTER JOIN
    (SELECT [Item No_], ISNULL(SUM(Quantity), 0) AS Inventory
    FROM [CompanyName$Item Ledger Entry] AS ILE
    GROUP BY [Item No_]) AS Table1 ON Item.No_ = Table1.[Item No_] LEFT OUTER JOIN
    (SELECT No_, [Document Type], Type, ISNULL(SUM([Outstanding Quantity]), 0) AS [Qty on Sales Order]
    FROM [CompanyName$Sales Line] AS SL
    GROUP BY No_, [Document Type], Type) AS Table2 ON Item.No_ = Table2.No_ AND Table2.[Document Type] = 1 AND
    Table2.Type = 2 LEFT OUTER JOIN
    (SELECT No_, [Document Type], Type, ISNULL(SUM([Outstanding Quantity]), 0) AS [Qty on Blanket Order]
    FROM [CompanyName$Sales Line] AS BSL
    GROUP BY No_, [Document Type], Type) AS Table3 ON Item.No_ = Table3.No_ AND Table3.[Document Type] = 4 AND Table3.Type = 2
    To get only those records from Item Table Where [Qty on Blanket Order] <> 0 OR [Qty on Sales Order] Where should i put this filter.



  2. Adriaan New Member

    I assume you mean, "where Qty_on_blanket_order <> 0 or Qty_on_sales_order <> 0", right?
    Since you must allow for either aggregate value to equal 0, you cannot filter within the derived tables.
    At the end of the main query, simply add that WHERE clause.
  3. alisag New Member

    Thnak you.
    Thats doubt i have but its clear now. Its working perfectly now.

Share This Page