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/11_data-dataset_record.sql

    r25154 r25198  
    166166/* check DSRs that don't have a dataset. */
    167167select * from TMP_DATASET_RECORD where DATASET_NAME is null;
    168 ==> 224 rows
    169168
    170169/* IVV names that don't match */
     
    180179  and INDICATOR_VIEW_NAME not in (select INDICATOR_VIEW_NAME from TMP_INDICATOR_VIEW_DATASET)
    181180;
    182 ==> removed the above 224 recs
    183181
    184182
     
    419417from DATASET_RECORD
    420418where MEASURE_VALUE is null
    421   and  (LABEL < " ")
     419  and ((LABEL is null) or (LABEL <= " "))
     420  and ((VALUE_ATTRIBUTE_NAME is null) or (VALUE_ATTRIBUTE_NAME < " "))
    422421group by LABEL, VALUE_ATTRIBUTE_NAME
    423422;
     
    425424delete from DATASET_RECORD
    426425where MEASURE_VALUE is null
    427   and ((LABEL is null) or (LABEL <= ""))
    428 ;
    429 ==> 1190
     426  and ((LABEL is null) or (LABEL <= " "))
     427  and ((VALUE_ATTRIBUTE_NAME is null) or (VALUE_ATTRIBUTE_NAME < " "))
     428;
    430429
    431430delete from DATASET_RECORD
     
    635634;
    636635*/
    637 
    638 
    639 /*
    640 issue appears to be dup keys for different IPVs with different measure values...
    641 */
    642 select ivv.INDICATOR_VIEW_NAME,
    643         ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE,
    644         ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE,
    645         ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
     636select count(ivv.INDICATOR_VIEW_NAME)
    646637from INDICATOR_VIEW_VALUE ivv
    647638left join TMP_DATASET_RECORD tdr on
     
    653644  and tdr.PERIOD_DIMENSION_NAME  = ivv.PERIOD_DIMENSION_NAME
    654645  and tdr.PERIOD_DIMENSION_VALUE = ivv.PERIOD_DIMENSION_VALUE
    655 order by INDICATOR_VIEW_NAME
    656 ;
    657 
     646;
     647
     648/* Possible issue of dup keys for different IPVs with different measure values. */
    658649select *
    659650from TMP_DATASET_RECORD
     
    697688
    698689
    699 
     690/* spot check */
    700691select INDICATOR_NAME, count(INDICATOR_VIEW_NAME) count_ivn,
    701692        MEASURE_NAME,
     
    712703;
    713704
    714 
    715705select INDICATOR_VIEW_NAME,
    716706        MEASURE_NAME,
     
    727717;
    728718
    729 
    730 
    731719select INDICATOR_NAME, count(INDICATOR_VIEW_NAME) count_ivn,
    732720        MEASURE_NAME,
     
    742730limit 5000
    743731;
    744 ==> 2451
    745 
    746 
    747 select distinct(INDICATOR_NAME) from
    748 (
    749 select INDICATOR_NAME from TMP_DATASET_RECORD
    750 group by INDICATOR_NAME, MEASURE_NAME,
    751         DIMENSION_1_NAME, DIMENSION_1_VALUE,
    752         DIMENSION_2_NAME, DIMENSION_2_VALUE,
    753         PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE
    754 having count(INDICATOR_VIEW_NAME) > 1
    755 ) as a
    756 ;
    757 ==> 114
    758 
    759 will need a mapping file for source IP with a new name and all the assoc
    760 views.  Script would then need to create all the data sources and other
    761 related intersections as well as renaming views and view values. 
     732
     733/* Show temp DSRs that have multiple view names */
     734select distinct(INDICATOR_NAME)
     735from
     736        (
     737                select INDICATOR_NAME from TMP_DATASET_RECORD
     738                group by INDICATOR_NAME, MEASURE_NAME,
     739                        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     740                        DIMENSION_2_NAME, DIMENSION_2_VALUE,
     741                        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE
     742                having count(INDICATOR_VIEW_NAME) > 1
     743        ) as a
     744;
    762745
    763746/*------------------------------ END OF FILE ------------------------------*/
Note: See TracChangeset for help on using the changeset viewer.