#sql #sql-server
Вопрос:
Мне трудно пройти мимо этого сообщения об ошибке.
Этот запрос работает:
declare @warehouseID int =-1, @type int=2, --0 = all; 1 = serial;2 = nonserial @LocationID int =0 SELECT Sum(Quantity) AS Quantity, WarehouseName, WarehouseID, sil.Location, max(si.RecordID) as test, min(si.RecordID) as test2, -- (SELECT max(TransactionDate) from SS_Inventory_History sih2 where sih2.InventoryID in (SI.RecordID) -- ) -- as test4, (select max(Case When Serial_Numbergt;'' Then COALESCE(si.SoldDate,'') else sih.TransactionDate end ) AS SoldDate FROM SS_Inventory SI JOIN SS_Inventory_Location SIL ON SIL.RecordID=SI.LocationID LEFT JOIN SS_Inventory_Warehouse SIW ON SIW.RecordID =SIL.WarehouseID Join SS_Inventory_History sih on InventoryID =sih.RecordID and sih.configStage = 7 WHERE SIL.WarehouseID = CASE WHEN @warehouseID = -1 THEN SIL.WarehouseID ELSE @warehouseID END AND SI.LocationID = CASE WHEN @LocationID = 0 THEN SI.LocationID ELSE @LocationID END AND ((si.Quantity=0 and Serial_Numbergt;'') or (Serial_Number='')) and SIL.Active=1 AND ( (@type = 0 ) OR (@type = 1 AND Serial_Number gt; '') OR (@type = 2 AND COALESCE(Serial_Number,'')='') ) ) AS SoldDate, max(si.ReceivedDate) AS ReceivedDate FROM SS_Inventory SI JOIN SS_Inventory_Location SIL ON SIL.RecordID=SI.LocationID LEFT JOIN SS_Inventory_Warehouse SIW ON SIW.RecordID =SIL.WarehouseID WHERE SIL.WarehouseID = CASE WHEN @warehouseID = -1 THEN SIL.WarehouseID ELSE @warehouseID END AND SI.LocationID = CASE WHEN @LocationID = 0 THEN SI.LocationID ELSE @LocationID END AND Quantitygt;0 and SIL.Active=1 AND ( (@type = 0 ) OR (@type = 1 AND Serial_Number gt; '') OR (@type = 2 AND COALESCE(Serial_Number,'')='') ) GROUP BY WarehouseName, WarehouseID, Location, Part_Number, Dist_Part_Num но когда я добавляю/раскомментирую
(SELECT max(TransactionDate) from SS_Inventory_History sih2 where sih2.InventoryID in (SI.RecordID) ) as test4, так что это выглядит как
declare @warehouseID int =-1, @type int=2, --0 = all; 1 = serial;2 = nonserial @LocationID int =0 SELECT Sum(Quantity) AS Quantity, WarehouseName, WarehouseID, sil.Location, --delete me? max(si.RecordID) as test, min(si.RecordID) as test2, (SELECT max(TransactionDate) from SS_Inventory_History sih2 where sih2.InventoryID in (SI.RecordID) ) as test4, (select max(Case When Serial_Numbergt;'' Then COALESCE(si.SoldDate,'') else sih.TransactionDate end ) AS SoldDate FROM SS_Inventory SI JOIN SS_Inventory_Location SIL ON SIL.RecordID=SI.LocationID LEFT JOIN SS_Inventory_Warehouse SIW ON SIW.RecordID =SIL.WarehouseID Join SS_Inventory_History sih on InventoryID =sih.RecordID and sih.configStage = 7 WHERE SIL.WarehouseID = CASE WHEN @warehouseID = -1 THEN SIL.WarehouseID ELSE @warehouseID END AND SI.LocationID = CASE WHEN @LocationID = 0 THEN SI.LocationID ELSE @LocationID END AND ((si.Quantity=0 and Serial_Numbergt;'') or (Serial_Number='')) and SIL.Active=1 AND ( (@type = 0 ) OR (@type = 1 AND Serial_Number gt; '') OR (@type = 2 AND COALESCE(Serial_Number,'')='') ) ) AS SoldDate, max(si.ReceivedDate) AS ReceivedDate FROM SS_Inventory SI JOIN SS_Inventory_Location SIL ON SIL.RecordID=SI.LocationID LEFT JOIN SS_Inventory_Warehouse SIW ON SIW.RecordID =SIL.WarehouseID WHERE SIL.WarehouseID = CASE WHEN @warehouseID = -1 THEN SIL.WarehouseID ELSE @warehouseID END AND SI.LocationID = CASE WHEN @LocationID = 0 THEN SI.LocationID ELSE @LocationID END AND Quantitygt;0 and SIL.Active=1 AND ( (@type = 0 ) OR (@type = 1 AND Serial_Number gt; '') OR (@type = 2 AND COALESCE(Serial_Number,'')='') ) GROUP BY WarehouseName, WarehouseID, Location, Part_Number, Dist_Part_Num Я получаю это сообщение об ошибке: Msg 8120, Уровень 16, Состояние 1, Строка 15 Столбец «SS_Inventory.RecordID» недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.
Кто-нибудь знает, что я делаю не так?
Комментарии:
1. Вместо этого ПРИСОЕДИНИТЕСЬ к подзапросу слева.
2. Спасибо, я думаю, что смог сделать это, используя этот метод
3. Базовое форматирование помогло бы. Этот запрос нечитаем