I am having an issue with this simple query. I get the error
“Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.”
x
SELECT TOP 100 PERCENT
dbo.Inventory.PARTNO
,( SELECT ISNULL(SUM(dbo.PurchaseOrderReceived.QtyReceived), 0)
FROM dbo.PurchaseOrderReceived
JOIN dbo.PurchaseOrderlineItems
ON dbo.PurchaseOrderReceived.POLIID = dbo.PurchaseOrderlineItems.POLIID
JOIN dbo.Inventory
ON dbo.PurchaseOrderlineItems.InvMasID = dbo.Inventory.InvMasID
JOIN dbo.Duties Duties_1 ON dbo.Inventory.DutyClass = Duties_1.DutyID
WHERE (Duties_1.DutyClass = 252)
AND (dbo.PurchaseOrderlineItems.Deleted = 0)
AND (dbo.PurchaseOrderReceived.ReceivedDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102))
GROUP BY dbo.Inventory.PARTNO
) AS Purchased
,ISNULL(SUM(dbo.OrderItems.QtyShipped), 0) AS Ordered
,ISNULL(SUM(DISTINCT dbo.MRP.QtyOnHand), 0) AS QOH
FROM dbo.Orders
JOIN dbo.OrderItems
JOIN dbo.Inventory
ON dbo.OrderItems.InvMasID = dbo.Inventory.InvMasID
ON dbo.Orders.OrderID = dbo.OrderItems.OrderID
JOIN dbo.MRP ON dbo.Inventory.InvMasID = dbo.MRP.InvMasID
JOIN dbo.Duties ON dbo.Inventory.DutyClass = dbo.Duties.DutyID
WHERE (dbo.Orders.ShipDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102))
AND (dbo.Duties.DutyClass = 252)
GROUP BY dbo.Inventory.PARTNO
I have tried working through this problem and know that there is a simple solution I am missing. The subquery, on its own, retrieves the information I am looking for, as does the main query, when separated. Thanks for any help!
Advertisement
Answer
put this condition AND dbo.Orders.PARTNO = dbo.Inventory.PARTNO and Top 1
SELECT TOP 100 PERCENT
dbo.Inventory.PARTNO
,( SELECT Top 1 ISNULL(SUM(dbo.PurchaseOrderReceived.QtyReceived), 0)
FROM dbo.PurchaseOr,derReceived
JOIN dbo.PurchaseOrderlineItems
ON dbo.PurchaseOrderReceived.POLIID = dbo.PurchaseOrderlineItems.POLIID
JOIN dbo.Inventory
ON dbo.PurchaseOrderlineItems.InvMasID = dbo.Inventory.InvMasID
JOIN dbo.Duties Duties_1 ON dbo.Inventory.DutyClass = Duties_1.DutyID
WHERE (Duties_1.DutyClass = 252)
AND (dbo.PurchaseOrderlineItems.Deleted = 0)
AND (dbo.PurchaseOrderReceived.ReceivedDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102))
AND dbo.Orders.PARTNO = dbo.Inventory.PARTNO
GROUP BY dbo.Inventory.PARTNO
) AS Purchased
,ISNULL(SUM(dbo.OrderItems.QtyShipped), 0) AS Ordered
,ISNULL(SUM(DISTINCT dbo.MRP.QtyOnHand), 0) AS QOH
FROM dbo.Orders
JOIN dbo.OrderItems
JOIN dbo.Inventory
ON dbo.OrderItems.InvMasID = dbo.Inventory.InvMasID
ON dbo.Orders.OrderID = dbo.OrderItems.OrderID
JOIN dbo.MRP ON dbo.Inventory.InvMasID = dbo.MRP.InvMasID
JOIN dbo.Duties ON dbo.Inventory.DutyClass = dbo.Duties.DutyID
WHERE (dbo.Orders.ShipDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102))
AND (dbo.Duties.DutyClass = 252)
GROUP BY dbo.Inventory.PARTNO