Imsmaenum Duplicate fix Part 2: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| Line 15: | Line 15: | ||
The upgrade scripts also set some of the existing enumvalues to '''inactive''' and add new values to the enumcategories e.g. ''CHA''. 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. <br/><br/> | The upgrade scripts also set some of the existing enumvalues to '''inactive''' and add new values to the enumcategories e.g. ''CHA''. 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. <br/><br/> | ||
[[Image: Duplicate enumvalue1.png | 350px]] <br/> | [[Image: Duplicate enumvalue1.png | 350px]] <br/> | ||
''Example of enumcategory with duplicates (Query | ''Example of enumcategory with duplicates (Query X)'' <br/> <br/> | ||
[[Image: Duplicate enumvalue2.png | 1000px]] <br/> | [[Image: Duplicate enumvalue2.png | 1000px]] <br/><br/> | ||
This query (Query Y) fixes the four enumcategories (which might be fixed now/NAA 2014-10-07): | This query (Query Y) fixes the four enumcategories (which might be fixed now/NAA 2014-10-07): | ||
| Line 38: | Line 38: | ||
# If you have any duplicate rows in your result set due to the 4 enumcategories, Query Y will fix that (which might be fixed now/NAA 2014-10-07) | # If you have any duplicate rows in your result set due to the 4 enumcategories, Query Y will fix that (which might be fixed now/NAA 2014-10-07) | ||
# Run Query X again. If you still have duplicates then do the steps below until you have no duplicates | # Run Query X again. If you still have duplicates then do the steps below until you have no duplicates ('''mandatory''') | ||
# Open table ''imsmaenum'' with Navicat and sort on ''enumcategory'' | # Open table ''imsmaenum'' with Navicat and sort on ''enumcategory'' | ||
# Compare the result set from '''before''' the upgrade scripts with current records | # Compare the result set from '''before''' the upgrade scripts with current records | ||
# Set ''isactive'' to t (TRUE) on the old rows if the country had them as Active in 5.08.04 and delete the new rows. | # Set ''isactive'' to t (TRUE) on the old rows if the country had them as Active in 5.08.04 and delete the new rows. | ||
# Repeat | # Repeat 4 - 5 until all are done and run Query 2 as double-check of no duplicates remain. | ||
[[Category:NoPublic]] | [[Category:NoPublic]] | ||
[[Category:SQL Queries]] | [[Category:SQL Queries]] | ||
[[Category:NAA]] | [[Category:NAA]] | ||
Revision as of 10:32, 7 October 2014
This query (Query X) lists the duplicate enumvalues created after the upgrade scripts. Note that there might be more enumcategories than the four enumcategories that you checked before the upgrade.
SELECT imsmaenum.enumcategory,
imsmaenum.enumvalue,
COUNT (imsmaenum.imsmaenum_guid)
FROM imsmaenum
GROUP BY imsmaenum.enumcategory,
imsmaenum.enumvalue
HAVING COUNT (imsmaenum.imsmaenum_guid) > 1
ORDER BY imsmaenum.enumcategory ASC,
imsmaenum.enumvalue ASC
The upgrade scripts wrongly add values to the four enumcategories so duplicates are created (This might be fixed now/NAA 2014-10-07).
The upgrade scripts also set some of the existing enumvalues to inactive and add new values to the enumcategories e.g. CHA. 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.
File:Duplicate enumvalue1.png
Example of enumcategory with duplicates (Query X)
This query (Query Y) fixes the four enumcategories (which might be fixed now/NAA 2014-10-07):
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);
- If you have any duplicate rows in your result set due to the 4 enumcategories, Query Y will fix that (which might be fixed now/NAA 2014-10-07)
- Run Query X again. If you still have duplicates then do the steps below until you have no duplicates (mandatory)
- Open table imsmaenum with Navicat and sort on enumcategory
- Compare the result set from before the upgrade scripts with current records
- Set isactive to t (TRUE) on the old rows if the country had them as Active in 5.08.04 and delete the new rows.
- Repeat 4 - 5 until all are done and run Query 2 as double-check of no duplicates remain.