How do you tune a query with outer apply? “Select 1: Only one SQL DML Statement allowed”

by Oct 19, 2019

Whenever we try to tune more complicated queries, we often get the error:

“Select 1: Only one SQL DML Statement allowed”

Below is an example. The tuner thinks the first OUTER APPLY is another statement, rather than part of the single statement. How can I change this so the tuner will look at it?


       i.No_ AS Sku,

       COALESCE(Stock.QuantityOnHand, 0) AS QuantityOnHand,

       i.[Qty_ In Stock (for Web)] AS FakeQuantity,

       COALESCE(Orders.QuantityOnOrder, 0) AS QuantityOnOrder,

    COALESCE(QtyonProdOrder.qty, 0) AS QuantityOnProdOrder

FROM [Item] i

Left join(

    select [Item No_], sum(cast([Remaining Quantity] as int)) ‘qty’

    from [Prod_ Order Component]

    where Status between ‘1’ and ‘3’

    group by [Item No_] ) QtyonProdOrder

    on i.No_ = QtyonProdOrder.[Item No_]

–Query Tuner doesn’t like the next line. Thinks its a second select.


       SELECT SUM(ile.[Remaining Quantity]) AS QuantityOnHand

       FROM [Item Ledger Entry] ile

       LEFT JOIN [Location] l ON ile.[Location Code] = l.Code

       WHERE ile.[Item No_] = i.No_

       AND (ile.[Open] IS NULL OR ile.[Open] = 1)

       AND (l.[Include for Available Calc] IS NULL OR l.[Include for Available Calc] = 1)

) Stock


       SELECT SUM(sl.[Outstanding Quantity]) AS QuantityOnOrder

       FROM [Sales Line] sl

       WHERE sl.[Document Type] = 1

       AND sl.No_ = i.No_

       GROUP BY sl.No_

) Orders;