Imsmaenum Duplicate fix: Difference between revisions

Latest revision as of 13:09, 3 May 2015

Duplicate enumvalues cause import issues i.e. empty fields, which are difficult to detect due to there are no error messages

This query should be exported as documentation of the values of which inactive enumvalues there before and after upgrade (NAA 2014-10-07):

SELECT DISTINCT
"public".imsmaenum.imsmaenum_guid,
"public".imsmaenum.enumcategory,
"public".imsmaenum.enumvalue,
"public".imsmaenum.widgettype,
"public".imsmaenum.seqno,
"public".imsmaenum.dataentrydate,
"public".imsmaenum.dataenterer,
"public".imsmaenum.isactive
FROM
"public".imsmaenum
WHERE
"public".imsmaenum.isactive = 'f'
ORDER BY
"public".imsmaenum.enumcategory ASC,
"public".imsmaenum.enumvalue ASC

{{#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

|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

|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

|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

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

}}}}}}

@@ Line 1: / Line 1: @@
 Duplicate enumvalues cause import issues i.e. empty fields, which are difficult to detect due to there are no error messages
-This query should be exported as documentation of the values of the four affected enumcategories:
+This query should be exported as documentation of the values of which inactive enumvalues there before and after upgrade (NAA 2014-10-07):
 <pre>
 SELECT DISTINCT
@@ Line 15: / Line 15: @@
 "public".imsmaenum
 WHERE
-"public".imsmaenum.enumcategory = 'Mine Action Activity' OR
+"public".imsmaenum.isactive = 'f'
-"public".imsmaenum.enumcategory = 'RecommendationType' OR
-"public".imsmaenum.enumcategory = 'Worksite' OR
-"public".imsmaenum.enumcategory = 'Theme Type'
 ORDER BY
 "public".imsmaenum.enumcategory ASC,
@@ Line 24: / Line 21: @@
 </pre>
-This query lists the duplicate enumvalues created after the upgrade scripts. Note that there might be '''more''' enumcategories than the four above:
-<pre>
-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
-</pre>
-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'''. 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>
-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
-# 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.
 {{NavBox Upgrade}}
 [[Category:NoPublic]]
 [[Category:SQL Queries]]
 [[Category:NAA]]

Imsmaenum Duplicate fix: Difference between revisions

Latest revision as of 13:09, 3 May 2015

Navigation menu

Search