Imsmaenum Duplicate fix Part 2: Difference between revisions

Revision as of 19:05, 7 October 2014

This query lists the duplicate enumvalues created after the upgrade scripts.

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 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 (Query X) lists the duplicate enumvalues created after the upgrade scripts.
+This query lists the duplicate enumvalues created after the upgrade scripts.
 <pre>
 SELECT  imsmaenum.enumcategory,
@@ Line 14: / Line 14: @@
 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/>
 [[Image: Duplicate enumvalue3.png | 300px]]<br/>
-''Example of enumcategory with duplicates (Query X)'' <br/> <br/>
+''Example of enumcategory with duplicates'' <br/> <br/>
 [[Image: Duplicate enumvalue2.png | 700px]] <br/><br/>
@@ Line 21: / Line 21: @@
 # 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 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}}

Imsmaenum Duplicate fix Part 2: Difference between revisions

Revision as of 19:05, 7 October 2014

Navigation menu

Search