Imsmaenum Duplicate fix Part 2: Difference between revisions

Latest revision as of 13:27, 30 April 2016

This query lists the duplicate enumvalues created after the upgrade scripts. Note that the functions lower will be used so duplicates like Evidence Point and Evidence point will be found, trim will find " Active" and "Active" and replace will find Cluster Accident and ClusterAccident.

SELECT
	imsmaenum.enumcategory,
	replace(lower(trim(imsmaenum.enumvalue)), ' ', ''),
	COUNT(imsmaenum.imsmaenum_guid)
FROM
	imsmaenum
GROUP BY
	imsmaenum.enumcategory,
replace(lower(trim(imsmaenum.enumvalue)), ' ', '')
HAVING
	COUNT(imsmaenum.imsmaenum_guid) > 1
ORDER BY
	1,2

The upgrade scripts 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 manually.

Example of enumcategory with duplicates

You need 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
If necessary 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 3 - 4 until all are done and run the query again as double-check of no duplicates remain.

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:imsmaenum duplicate fix part 2

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:imsmaenum duplicate fix part 2

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:imsmaenum duplicate fix part 2

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:imsmaenum duplicate fix part 2

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}

@@ Line 1: / Line 1: @@
-This query 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:
+This query lists the duplicate enumvalues created after the upgrade scripts. Note that the functions ''lower'' will be used so duplicates like Evidence Point and Evidence point will be found, ''trim'' will find " Active" and "Active" and ''replace'' will find Cluster Accident and ClusterAccident.
 <pre>
 SELECT
-        imsmaenum.enumcategory,
+	imsmaenum.enumcategory,
-        imsmaenum.enumvalue,
+	replace(lower(trim(imsmaenum.enumvalue)), ' ', ''),
-        COUNT (imsmaenum.imsmaenum_guid)
+	COUNT(imsmaenum.imsmaenum_guid)
 FROM
-        imsmaenum
+	imsmaenum
 GROUP BY
-        imsmaenum.enumcategory,
+	imsmaenum.enumcategory,
-        imsmaenum.enumvalue
+replace(lower(trim(imsmaenum.enumvalue)), ' ', '')
 HAVING
-        COUNT (
+	COUNT(imsmaenum.imsmaenum_guid) > 1
-                imsmaenum.imsmaenum_guid
-        ) > 1
 ORDER BY
-        imsmaenum.enumcategory ASC,
+,2
-        imsmaenum.enumvalue ASC
 </pre>
-The upgrade scripts wrongly add values to the four enumcategories so duplicates are created (This might be fixed now/NAA 2014-10-07).<br/>
-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. <br/><br/>
-[[Image: Duplicate enumvalue1.png | 350px]] <br/>
-''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>
-[[Image: Duplicate enumvalue2.png | 1000px]] <br/>
-<pre>
+The upgrade scripts 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 manually. <br/><br/>
-Delete
+[[Image: Duplicate enumvalue3.png | 300px]]<br/>
-From imsmaenum a
+''Example of enumcategory with duplicates'' <br/> <br/>
-Where imsmaenum_guid in ('0a0a-224d-1416fd019a4-b88d2be0-0-4cc1', '0a0a-224d-1416fd019a4-d726279e-1-1e59',
+[[Image: Duplicate enumvalue2.png | 700px]] <br/><br/>
-'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',
+# You need do the steps below until you have no duplicates ('''mandatory''')
-'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
-# 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.
+# If necessary 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 3 - 4 until all are done and run Query 2 as double-check of no duplicates remain.
+# Repeat 3 - 4 until all are done and run the query again as double-check of no duplicates remain.
+{{NavBox Upgrade}}
 [[Category:NoPublic]]
 [[Category:SQL Queries]]
 [[Category:NAA]]

Imsmaenum Duplicate fix Part 2: Difference between revisions

Latest revision as of 13:27, 30 April 2016

Navigation menu

Search