Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
sms1_-_repairing_key_violations [2019/04/03 15:05]
admin [SQL 2] 		sms1_-_repairing_key_violations [2019/04/03 15:06] (current)
admin [Task Information]
Line 114: Line 114:
 <code sql> <code sql>
 // RETURN Orders that have no line items // RETURN Orders that have no line items
-SELECT "Missing OrderID" AS MissingType, OrderID AS ID, STATUS, ClosedDate+SELECT CAST("Missing OrderID" as Char(30)) AS MissingType, OrderID AS ID, Status, ClosedDate
 FROM "Order Database" FROM "Order Database"
-WHERE OrderID NOT IN (SELECT OrderID FROM "Order Details Database")+WHERE OrderID NOT IN (SELECT OrderID FROM "Order Details Database") AND OrderID > 0
 UNION UNION
 // RETURN Estimates that have no line items // RETURN Estimates that have no line items
-SELECT "Missing EstimateID" AS MissingType, OrderID AS ID, STATUS, ClosedDate+SELECT CAST("Missing EstimateID" as Char(30)) AS MissingType, OrderID AS ID, Status, ClosedDate
 FROM "Estimate Database" FROM "Estimate Database"
-WHERE OrderID NOT IN (SELECT OrderID FROM "Estimate Details Database")+WHERE OrderID NOT IN (SELECT OrderID FROM "Estimate Details Database") AND OrderID > 0 
 +UNION 
 +SELECT CAST("Missing Order ProductCode" as Char(30)) AS MissingType, OrderID AS ID, Status, ClosedDate 
 +FROM "Order Database" 
 +WHERE OrderID IN (SELECT OrderID FROM "Order Details Database" WHERE ProductCode = '(none)') AND OrderID > 0 
 +UNION 
 +SELECT CAST("Missing Estimate ProductCode" as Char(30)) AS MissingType, OrderID AS ID, Status, ClosedDate 
 +FROM "Estimate Database" 
 +WHERE OrderID IN (SELECT OrderID FROM "Estimate Details Database" WHERE ProductCode = '(none)') AND OrderID > 0
 ORDER BY MissingType DESC, OrderID DESC ORDER BY MissingType DESC, OrderID DESC
 </code> </code>
Line 139: Line 147:
  
 ====== Task Information ====== ====== Task Information ======
-  * Last Updated: 2/15/2015+  * Last Updated: 4/03/2019
   * Created By: Michael Nolan   * Created By: Michael Nolan
-  * Verified By: 
   * Applies in all versions. More common in versions SMS 8.6 and below due to a change in how Keys are handled for orders and estimates in SMS 8.9.   * Applies in all versions. More common in versions SMS 8.6 and below due to a change in how Keys are handled for orders and estimates in SMS 8.9.