I have a query that runs in SQL Server (using Visual Studio 19), but when I try to run it using Microsoft Query in Excel, I get the error
"No column name was specified for column 5 of 'bv'.
"No column name was specified for column 6 of 'bv'.
"No column name was specified for column 7 of 'bv'.
Statement(s) could not be prepared.
As you can see below, I alias columns 5, 6, and 7 (Pieces, BatchValue, Multiplier). In addition, the fact that this query runs in Visual Studio indicates I haven't gotten this egregiously wrong.
WITH bv AS
(
SELECT
b.BatchID, b.BatchDate, b.BatchName, b.Price AS Cost,
SUM(bh.QtyFound) AS Pieces,
SUM(ps.StrategyPrice * QtyFound) AS BatchValue,
IIF(b.Price = 0, 0, SUM(ps.StrategyPrice * QtyFound) / b.Price) AS Multiplier,
b.Active
FROM
Inventory.Batches b
JOIN
Inventory_Item_History_Summary bh ON b.BatchID = bh.BatchID
JOIN
Selling.Price_Strategy_Table ps ON LEFT(bh.ItemType, 1) = LEFT(ps.ItemType, 1)
AND bh.ItemNum = ps.ItemNo
AND bh.ColorID = ps.Color
AND bh.BLCond = ps.Cond
GROUP BY
b.BatchID, b.BatchDate, b.BatchName, Price, Active
)
SELECT
SUM(p.StrategyPrice * h.QtyFound) / SUM(p.StrategyPrice * IIF(bv.Multiplier = 0, 0, h.QtyFound / bv.Multiplier)) AvgMultiplier
FROM
Inventory.Locations l
JOIN
Inventory_Item_History_Summary h ON h.LocationID = l.LocationID
JOIN
Selling.Price_Strategy_Table p ON LEFT(h.ItemType, 1) = LEFT(p.ItemType, 1)
AND h.ItemNum = p.ItemNo
AND h.ColorID = p.Color
AND h.BLCond = p.Cond
JOIN
bv ON h.BatchID = bv.BatchID
WHERE
l.Cond = 'U'
AND bv.cost > 0
AND bv.Active = 0
What have I done wrong to cause it not to run in Microsoft Query? It's worth noting that I have another query on the same worksheet that uses the exact same bv subquery with no issues