Changeset 25198 in main for trunk/db/src/main/scripts/mysql/migrate/1_data-cleanup.sql

Timestamp:

05/04/22 19:36:40 (3 months ago)

Author:

GarthBraithwaite_STG

Message:

db - updated the ds create to key off of SO3 and "Country" and cleaned out dup SO1 and SO3 usage. Added map_name cleanup to set to None vs DEFAULT. Added more select tests. Added test for VALUE_ATTR and null measure value.

File:

• trunk/db/src/main/scripts/mysql/migrate/1_data-cleanup.sql (modified) (2 diffs)

trunk/db/src/main/scripts/mysql/migrate/1_data-cleanup.sql

@@ -6 +6 @@
         has been complteted.
 
-        NOTE: When mixing myisam tables need to use "collate" so that character set 
-        data can be compared.  If using .sql exports then not needed.
+        Also, not removing apparent orphaned IVV records as these are manually set
+        by Kim for special splitting of views to have a "constant series".
 */
-
 
 /* REMOVE ORPHANED IPVs: delete invalid or orphaned IV records */
 delete from INDICATOR_VIEW where INDICATOR_NAME not in (select NAME from INDICATOR);
-delete from INDICATOR_VIEW 
-where DATASET_CATEGORY_NAME is null 
-   or DATASET_CATEGORY_NAME not in (select NAME from DATASET_CATEGORY)
-   or DATASET_SERIES_NAME   not in (select NAME from DATASET_SERIES)
-;
 
 /* REMOVE ORPHANED IVV: 
@@ -28 +22 @@
 delete from INDICATOR_VIEW_VALUE 
 where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW)
-;
-
-delete from INDICATOR_VIEW_VALUE 
-where DATASET_CATEGORY_NAME  is null
-   or DATASET_CATEGORY_NAME   != (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
-   or DATASET_SERIES_NAME     != (select DATASET_SERIES_NAME   from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
-   or DATASET_SERIES_VALUE   not in (select VALUE from DATASET_SERIES_VALUE dsv   where dsv.DATASET_SERIES_NAME   = INDICATOR_VIEW_VALUE.DATASET_SERIES_NAME)
-   or DATASET_CATEGORY_VALUE not in (select VALUE from DATASET_CATEGORY_VALUE dcv where dcv.DATASET_CATEGORY_NAME = INDICATOR_VIEW_VALUE.DATASET_CATEGORY_NAME)
 ;