Differences

This shows you the differences between two versions of the page.

Link to this comparison view

— control_sql_-_fix_inventorylog_to_point_to_proper_inventory_record [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +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.
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**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**.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +-- 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)
 + )
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 2/2011
 +  * Version : 4.5+
 +
 +
 +