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.”
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