This SQL will correct a problem in the InventoryLog table where the InventoryID it points to is a warehouse or division summary and not the actual InventoryID.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

-- query to make sure the InventoryID of the InventoryLog points to the correct
-- Inventory item.  Due to something (an old bug?), it appears to point to an old warehouse record
-- for that inventory item
-- get the correct inventoryID from the part 
UPDATE InventoryLog
SET InventoryClassTypeID = 12200,
    InventoryID = 
    (
		SELECT 	ID AS InventoryID
				--ClassTypeID as InventoryClassTypeID,
				--coalesce(DivisionID, 10) as DivisionID, 
				--coalesce(WarehouseID, 10) as WarehouseID
		FROM Inventory I
		WHERE ClassTypeID = 12200 
			AND I.PartID = InventoryLog.PartID
			AND COALESCE(I.DivisionID, 10) = COALESCE(InventoryLog.FromDivisionID, 10)
			AND COALESCE(I.WarehouseID, 10) = COALESCE(InventoryLog.FromWarehouseID, 10)
	)
WHERE InventoryID   
	(
	SELECT 	ID AS InventoryID
			--ClassTypeID as InventoryClassTypeID,
			--coalesce(DivisionID, 10) as DivisionID, 
			--coalesce(WarehouseID, 10) as WarehouseID
	FROM Inventory I
	WHERE ClassTypeID = 12200 
		AND I.PartID = InventoryLog.PartID
		AND COALESCE(I.DivisionID, 10) = COALESCE(InventoryLog.FromDivisionID, 10)
		AND COALESCE(I.WarehouseID, 10) = COALESCE(InventoryLog.FromWarehouseID, 10)
	)
  • Entered : 2/2011
  • Version : 4.5+
You could leave a comment if you were logged in.