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?
SELECT
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.
OUTER APPLY (
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
OUTER APPLY (
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;