Imsmaenum Duplicate fix Part 2 - Revision history

Alnaucler at 12:27, 30 April 2016

2016-04-30T12:27:45Z

← Older revision		Revision as of 14:27, 30 April 2016
Line 1:		Line 1:
	This query lists the duplicate enumvalues created after the upgrade scripts ~~(ICR-297)~~. 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.		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>		<pre>
	SELECT		SELECT

Alnaucler at 12:21, 30 April 2016

2016-04-30T12:21:52Z

← Older revision		Revision as of 14:21, 30 April 2016
Line 1:		Line 1:
	This query lists the duplicate enumvalues created after the upgrade scripts.		This query lists the duplicate enumvalues created after the upgrade scripts (ICR-297). 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>		<pre>
	SELECT imsmaenum.enumcategory,		SELECT
	imsmaenum.enumvalue,		imsmaenum.enumcategory,
	COUNT (imsmaenum.imsmaenum_guid)		replace(lower(trim(imsmaenum.enumvalue)), ' ', ''),
	FROM imsmaenum		COUNT(imsmaenum.imsmaenum_guid)
	GROUP BY imsmaenum.enumcategory,		FROM
	imsmaenum.enumvalue		imsmaenum
	HAVING COUNT (imsmaenum.imsmaenum_guid) > 1		GROUP BY
	ORDER BY ~~imsmaenum.enumcategory ASC~~,		imsmaenum.enumcategory,
	~~imsmaenum.enumvalue ASC~~		replace(lower(trim(imsmaenum.enumvalue)), ' ', '')
			HAVING
			COUNT(imsmaenum.imsmaenum_guid) > 1
			ORDER BY
			1,2
	</pre>		</pre>

Alnaucler at 18:05, 7 October 2014

2014-10-07T18:05:12Z

Alnaucler at 18:01, 7 October 2014

2014-10-07T18:01:48Z

← Older revision		Revision as of 20:01, 7 October 2014
Line 1:		Line 1:
	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~~.		This query (Query X) lists the duplicate enumvalues created after the upgrade scripts.
	<pre>		<pre>
	SELECT imsmaenum.enumcategory,		SELECT imsmaenum.enumcategory,
Line 12:		Line 12:
	</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).<br/>~~		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/>
	~~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 enumvalue3.png \| 300px]]<br/>
	~~[[Image: Duplicate enumvalue1.png \| 350px]]~~ [[Image: Duplicate enumvalue3.png \| 300px]]<br/>
	''Example of enumcategory with duplicates (Query X)'' <br/> <br/>		''Example of enumcategory with duplicates (Query X)'' <br/> <br/>
	[[Image: Duplicate enumvalue2.png \| 700px]] <br/><br/>		[[Image: Duplicate enumvalue2.png \| 700px]] <br/><br/>

	~~This query (Query Y) fixes the four enumcategories (which might be fixed now/NAA 2014-10-07):~~		# You need do the steps below until you have no duplicates ('''mandatory''')
	~~<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>~~

	# ~~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''		# 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.		# 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 4 ~~- 5~~ 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 11:50, 7 October 2014

2014-10-07T11:50:54Z

← Older revision		Revision as of 13:50, 7 October 2014
Line 16:		Line 16:
	[[Image: Duplicate enumvalue1.png \| 350px]] [[Image: Duplicate enumvalue3.png \| 300px]]<br/>		[[Image: Duplicate enumvalue1.png \| 350px]] [[Image: Duplicate enumvalue3.png \| 300px]]<br/>
	''Example of enumcategory with duplicates (Query X)'' <br/> <br/>		''Example of enumcategory with duplicates (Query X)'' <br/> <br/>
	[[Image: Duplicate enumvalue2.png \| ~~1000px~~]] <br/><br/>		[[Image: Duplicate enumvalue2.png \| 700px]] <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):

Alnaucler at 11:21, 7 October 2014

2014-10-07T11:21:05Z

← Older revision		Revision as of 13:21, 7 October 2014
Line 14:		Line 14:
	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 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''' 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]] [[Image: Duplicate enumvalue3.png \| ~~325px~~]]<br/>		[[Image: Duplicate enumvalue1.png \| 350px]] [[Image: Duplicate enumvalue3.png \| 300px]]<br/>
	''Example of enumcategory with duplicates (Query X)'' <br/> <br/>		''Example of enumcategory with duplicates (Query X)'' <br/> <br/>
	[[Image: Duplicate enumvalue2.png \| 1000px]] <br/><br/>		[[Image: Duplicate enumvalue2.png \| 1000px]] <br/><br/>

Alnaucler at 11:20, 7 October 2014

2014-10-07T11:20:46Z

← Older revision		Revision as of 13:20, 7 October 2014
Line 14:		Line 14:
	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 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''' 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]] [[Image: Duplicate enumvalue3.png \| ~~350px~~]]<br/>		[[Image: Duplicate enumvalue1.png \| 350px]] [[Image: Duplicate enumvalue3.png \| 325px]]<br/>
	''Example of enumcategory with duplicates (Query X)'' <br/> <br/>		''Example of enumcategory with duplicates (Query X)'' <br/> <br/>
	[[Image: Duplicate enumvalue2.png \| 1000px]] <br/><br/>		[[Image: Duplicate enumvalue2.png \| 1000px]] <br/><br/>

Alnaucler at 09:59, 7 October 2014

2014-10-07T09:59:35Z

← Older revision		Revision as of 11:59, 7 October 2014
Line 14:		Line 14:
	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 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''' 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]] [[Image: Duplicate enumvalue3.png \| 350px]]<br/>
	''Example of enumcategory with duplicates (Query X)'' <br/> <br/>		''Example of enumcategory with duplicates (Query X)'' <br/> <br/>
	[[Image: Duplicate enumvalue2.png \| 1000px]] <br/><br/>		[[Image: Duplicate enumvalue2.png \| 1000px]] <br/><br/>

Alnaucler at 09:51, 7 October 2014

2014-10-07T09:51:25Z

← Older revision		Revision as of 11:51, 7 October 2014
Line 44:		Line 44:
	# Repeat 4 - 5 until all are done and run Query 2 as double-check of no duplicates remain.		# Repeat 4 - 5 until all are done and run Query 2 as double-check of no duplicates remain.

			{{NavBox Upgrade}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:SQL Queries]]		[[Category:SQL Queries]]
	[[Category:NAA]]		[[Category:NAA]]

Alnaucler at 09:32, 7 October 2014

2014-10-07T09:32:45Z

← Older revision		Revision as of 20:05, 7 October 2014
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>		<pre>
	SELECT imsmaenum.enumcategory,		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/>		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/>		[[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/>		[[Image: Duplicate enumvalue2.png \| 700px]] <br/><br/>

Line 21:		Line 21:
	# Compare the result set from '''before''' the upgrade scripts with current records		# 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.		# 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}}		{{NavBox Upgrade}}

← Older revision		Revision as of 11:32, 7 October 2014
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 1)'' <br/> <br/>		''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 3 - 4 until all are done and run Query 2 as double-check of no duplicates remain.		# 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]]