Ignore:
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:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/db/src/main/scripts/mysql/migrate/1_data-cleanup.sql

    r25052 r25198  
    66        has been complteted.
    77
    8         NOTE: When mixing myisam tables need to use "collate" so that character set
    9         data can be compared.  If using .sql exports then not needed.
     8        Also, not removing apparent orphaned IVV records as these are manually set
     9        by Kim for special splitting of views to have a "constant series".
    1010*/
    11 
    1211
    1312/* REMOVE ORPHANED IPVs: delete invalid or orphaned IV records */
    1413delete from INDICATOR_VIEW where INDICATOR_NAME not in (select NAME from INDICATOR);
    15 delete from INDICATOR_VIEW
    16 where DATASET_CATEGORY_NAME is null
    17    or DATASET_CATEGORY_NAME not in (select NAME from DATASET_CATEGORY)
    18    or DATASET_SERIES_NAME   not in (select NAME from DATASET_SERIES)
    19 ;
    2014
    2115/* REMOVE ORPHANED IVV:
     
    2822delete from INDICATOR_VIEW_VALUE
    2923where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW)
    30 ;
    31 
    32 delete from INDICATOR_VIEW_VALUE
    33 where DATASET_CATEGORY_NAME  is null
    34    or DATASET_CATEGORY_NAME   != (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
    35    or DATASET_SERIES_NAME     != (select DATASET_SERIES_NAME   from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
    36    or DATASET_SERIES_VALUE   not in (select VALUE from DATASET_SERIES_VALUE dsv   where dsv.DATASET_SERIES_NAME   = INDICATOR_VIEW_VALUE.DATASET_SERIES_NAME)
    37    or DATASET_CATEGORY_VALUE not in (select VALUE from DATASET_CATEGORY_VALUE dcv where dcv.DATASET_CATEGORY_NAME = INDICATOR_VIEW_VALUE.DATASET_CATEGORY_NAME)
    3824;
    3925
Note: See TracChangeset for help on using the changeset viewer.