Imsmaenum Duplicate fix - Revision history

Alnaucler at 12:09, 3 May 2015

2015-05-03T12:09:39Z

← Older revision		Revision as of 14:09, 3 May 2015
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		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 that the~~ upgrade ~~scripts wrongly creates inactive values for~~ (NAA 2014-10-07):		This query should be exported as documentation of the values of which inactive enumvalues there before and after upgrade (NAA 2014-10-07):
	<pre>		<pre>
	SELECT DISTINCT		SELECT DISTINCT

Alnaucler at 14:56, 18 March 2015

2015-03-18T14:56:38Z

← Older revision		Revision as of 16:56, 18 March 2015
Line 15:		Line 15:
	"public".imsmaenum		"public".imsmaenum
	WHERE		WHERE
	~~("public".imsmaenum.enumcategory = 'Mine Action Activity' OR~~
	~~"public".imsmaenum.enumcategory = 'RecommendationType' OR~~
	~~"public".imsmaenum.enumcategory = 'Worksite' OR~~
	~~"public".imsmaenum.enumcategory = 'Theme Type') AND~~
	"public".imsmaenum.isactive = 'f'		"public".imsmaenum.isactive = 'f'
	ORDER BY		ORDER BY

Alnaucler at 17:50, 7 October 2014

2014-10-07T17:50:26Z

← Older revision		Revision as of 19:50, 7 October 2014
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		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 that the upgrade ~~script~~ wrongly creates ~~duplicates~~ for (~~This might be fixed now/~~NAA 2014-10-07):		This query should be exported as documentation of the values of the four affected enumcategories that the upgrade scripts wrongly creates inactive values for (NAA 2014-10-07):
	<pre>		<pre>
	SELECT DISTINCT		SELECT DISTINCT

Alnaucler at 17:49, 7 October 2014

2014-10-07T17:49:29Z

← Older revision		Revision as of 19:49, 7 October 2014
Line 15:		Line 15:
	"public".imsmaenum		"public".imsmaenum
	WHERE		WHERE
	"public".imsmaenum.enumcategory = 'Mine Action Activity' OR		("public".imsmaenum.enumcategory = 'Mine Action Activity' OR
	"public".imsmaenum.enumcategory = 'RecommendationType' OR		"public".imsmaenum.enumcategory = 'RecommendationType' OR
	"public".imsmaenum.enumcategory = 'Worksite' OR		"public".imsmaenum.enumcategory = 'Worksite' OR
	"public".imsmaenum.enumcategory = 'Theme Type'		"public".imsmaenum.enumcategory = 'Theme Type') AND
			"public".imsmaenum.isactive = 'f'
	ORDER BY		ORDER BY
	"public".imsmaenum.enumcategory ASC,		"public".imsmaenum.enumcategory ASC,

Alnaucler at 09:20, 7 October 2014

2014-10-07T09:20:19Z

@@ Line 24: / Line 24: @@
 </pre>
 {{NavBox Upgrade}}
 [[Category:NoPublic]]
 [[Category:SQL Queries]]
 [[Category:NAA]]

Alnaucler at 09:00, 7 October 2014

2014-10-07T09:00:04Z

← Older revision		Revision as of 11:00, 7 October 2014
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		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 the four affected enumcategories that the upgrade script wrongly creates duplicates for (This might be fixed now/NAA 2014-10-07):
	<pre>		<pre>
	SELECT DISTINCT		SELECT DISTINCT
Line 43:		Line 43:
	imsmaenum.enumvalue ASC		imsmaenum.enumvalue ASC
	</pre>		</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/>		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/>		[[Image: Duplicate enumvalue1.png \| 350px]] <br/>
	''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>		''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>

Alnaucler at 08:57, 7 October 2014

2014-10-07T08:57:09Z

← Older revision		Revision as of 10:57, 7 October 2014
Line 24:		Line 24:
	</pre>		</pre>

	This query lists the duplicate enumvalues created after the upgrade scripts. Note that there might be more enumcategories than the four above:		This query lists the duplicate enumvalues created after the upgrade scripts. Note that there might be '''more''' enumcategories than the four above:
	<pre>		<pre>
	SELECT		SELECT
Line 43:		Line 43:
	imsmaenum.enumvalue ASC		imsmaenum.enumvalue ASC
	</pre>		</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. <br/><br/>		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/>		[[Image: Duplicate enumvalue1.png \| 350px]] <br/>
	''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>		''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>
Line 68:		Line 68:
	# 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		# 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 Query 2 as double-check of no duplicates remain.
	{{NavBox Upgrade}}		{{NavBox Upgrade}}

Alnaucler at 17:24, 16 July 2014

2014-07-16T17:24:18Z

← Older revision		Revision as of 19:24, 16 July 2014
Line 43:		Line 43:
	imsmaenum.enumvalue ASC		imsmaenum.enumvalue ASC
	</pre>		</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 manually. <br/><br/>		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. <br/><br/>
	[[Image: Duplicate enumvalue1.png \| 350px]] <br/>		[[Image: Duplicate enumvalue1.png \| 350px]] <br/>
	''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>		''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>
	[[Image: Duplicate enumvalue2.png \| 1000px]] <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''		# 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
	~~# 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 in 5.08.04
	# Set ''isactive'' to t (TRUE) on the old rows if the country had them as ~~active~~ in 5.08.04		# Repeat 3 - 4 until all are done and run Query 2 as double-check of no duplicates remain.
	# Repeat 2- 4 until all are done and run Query 2 as double-check of no duplicates remain.
	{{NavBox Upgrade}}		{{NavBox Upgrade}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:SQL Queries]]		[[Category:SQL Queries]]
	[[Category:NAA]]		[[Category:NAA]]

Alnaucler at 12:57, 8 July 2014

2014-07-08T12:57:38Z

← Older revision		Revision as of 14:57, 8 July 2014
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 the four affected enumcategories:
	<pre>		<pre>

Dionysia at 14:23, 7 July 2014

2014-07-07T14:23:55Z

← Older revision		Revision as of 16:23, 7 July 2014
Line 41:		Line 41:
	imsmaenum.enumvalue ASC		imsmaenum.enumvalue ASC
	</pre>		</pre>
	The upgrade scripts wrongly add values to the four enumcategories so duplicates are created. The upgrade scripts also ~~sets~~ 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 manually. <br/><br/>		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 manually. <br/><br/>
	[[Image: Duplicate enumvalue1.png \| 350px]] <br/>		[[Image: Duplicate enumvalue1.png \| 350px]] <br/>
	''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>		''Example of enumcategory with duplicates (Query 1)'' <br/> <br/>