1

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.

enter image description here

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
Scott
  • 3,663
  • 8
  • 33
  • 56
  • Coincidence that the errors start on the second line? Check for stray characters or formatting at the end of line 1. – L. Scott Johnson Sep 03 '21 at 15:29
  • It was actually all in one line before - I broke it up for readability on SO – Scott Sep 03 '21 at 15:31
  • 1
    I'm willing to bet that this is Excel or the provider getting confused with the CTE. Does it work if you specifically spell out the columns in the CTE definition? `WITH bv (BatchID, BatchDate, BatchName, Cost, BatchValue, Multiplier, Active) AS (...` – squillman Sep 03 '21 at 15:34
  • 1
    If the problem is the `WITH`, you could try to convert `bv` into a sub-select. `JOIN (SELECT b.BatchID, ...) AS bv ON h.BatchID=bv.BatchID` – Olivier Jacot-Descombes Sep 03 '21 at 15:37
  • The error message itself could be a red herring. You don't always qualify the `Active`, `Price` and `QtyFound` columns - do they appear in multiple tables? – AlwaysLearning Sep 06 '21 at 03:22

0 Answers0