Changes

Imsmaenum Duplicate fix

1,165 bytes added, 19:24, 16 July 2014

no edit summary

imsmaenum.enumvalue ASC

</pre>

The upgrade scripts wrongly add values to the four enumcategories so duplicates are created. The upgrade scripts also set some of the existing enumvalues to '''inactive'''. Since all countries may have different duplicates and different options set to inactive there is no fit-all solution and therefore the duplicates enumvalues are best fixed semi-manually.

[[Image: Duplicate enumvalue1.png | 350px]]

''Example of enumcategory with duplicates (Query 1)''

[[Image: Duplicate enumvalue2.png | 1000px]]

<pre>

Delete

From imsmaenum a

Where imsmaenum_guid in ('0a0a-224d-1416fd019a4-b88d2be0-0-4cc1', '0a0a-224d-1416fd019a4-d726279e-1-1e59',

'0a0a-224d-1416fd019a4-706a50d3-3-612d', '0a0a-224d-1416fd019a4-af464191-4-c125', '0a0a-224d-1416fd019a4-fd146973-5-20fc',

'0a0a-224d-1416fd019a4-f226cffb-6-e6cf', '0a0a-224d-1416fd019a4-683d623a-7-777c', '0a0a-224d-1416fd019a4-3bde48c9-8-b54c',

'0a0a-224d-1416fd019a4-ecbeac5e-9-bacd', '0a0a-224d-1416fd019a4-d9d99edf-a-9e90', '0a0a-224d-1416fd019a4-f68dd115-b-20a1',

'0a0a-224d-1416fd019a4-4d707100-c-4d01', '0a0a-224d-1416fd019a4-578f86a7-d-b65f', '0a0a-224d-1416fd019a4-a8c57e35-e-9bce',

'0a0a-224d-1416fd019a4-bc85b8d0-f-ce2a', '0a0a-224d-1416fd019a4-b8a8e92e-10-8d9', '0a0a-224d-1416fd019a4-1f1e8407-11-b89',

'0a0a-224d-1416fd019a4-f581855a-12-7ad', '0a0a-224d-1416fd019a4-2947602a-13-888', '0a0a-224d-1416fd019a4-8c31904c-14-cbd',

'0a0a-224d-1416fd019a4-cc3c4be8-15-7fd', '0a0a-224d-1416fd019a4-7469e71b-16-868', '0a0a-2256-14150c2ec4d-5c24e7d6-c8-18c',

'0a0a-223c-141556773b4-2eafbfc-0-5c22c', '0a0a-223c-141556773b5-cdf8cf63-1-f947')

And ((Select count(*)

From imsmaenum b

Where b.enumvalue = a.enumvalue and b.enumcategory = a.enumcategory)

> 1);

</pre>

# Run first this query which deleted the wrongly created duplicates

# Open table ''imsmaenum'' with Navicat and sort on ''enumcategory''

# Compare the result set from '''before''' the upgrade scripts with current records

~~# Delete the new rows, in the example created with user ''migration'', by selecting the row and clicking on delete button~~# Set ''isactive'' to t (TRUE) on the old rows if the country had them as ~~active~~ Active in 5.08.04# Repeat 23 - 4 until all are done and run Query 2 as double-check of no duplicates remain.

[[Category:NoPublic]]

[[Category:SQL Queries]]

[[Category:NAA]]

Alnaucler

NoPublic, bureaucrat, administrator

6,632

edits