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/8_data-iv_dimension_usage.sql

    r25052 r25198  
    7777
    7878
    79 
    8079/* Test that all views have a period dimension used.  */
    8180select NAME, ACTIVE_FLAG
     
    9392        )
    9493;
    95 /*
    96 ==> 321 that don't have an entry.  This  is because the PD is not used as a
    97 series or cat. 
    98 */
     94
    9995
    10096/* Insert PDN as a constant.  Later will insert the value that matches the
     
    104100select NAME, PERIOD_DIMENSION_NAME, 'constant'
    105101from INDICATOR_VIEW
    106 where not exists
    107         (select 1
     102where NAME not in
     103        (select INDICATOR_VIEW_NAME
    108104         from DIMENSION_TO_INDICATOR_VIEW
    109105         where INDICATOR_VIEW_NAME = NAME
     
    111107        )
    112108;
     109
     110
     111/* Test for only a single IV to DIM usage.  Min is 2 (period and dim1). */
     112select * from DIMENSION_TO_INDICATOR_VIEW
     113group by INDICATOR_VIEW_NAME
     114having count(*) = 1
     115;
     116
     117
     118/* Insert State as the default constant dim where a "Year" is the category and
     119        there are not any other dimensions set.
     120*/
     121insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
     122select NAME, 'State', 'constant'
     123from INDICATOR_VIEW
     124where NAME in
     125        (select INDICATOR_VIEW_NAME
     126         from DIMENSION_TO_INDICATOR_VIEW
     127         where INDICATOR_VIEW_NAME = NAME
     128           and DIMENSION_NAME  like 'Year%'
     129           and DIMENSION_USAGE = 'category'
     130        )
     131  and NAME in
     132        (select INDICATOR_VIEW_NAME
     133         from DIMENSION_TO_INDICATOR_VIEW
     134         group by INDICATOR_VIEW_NAME
     135         having count(*) = 1
     136        )
     137;
     138
     139/* Do the single dim test again */
     140select * from DIMENSION_TO_INDICATOR_VIEW
     141group by INDICATOR_VIEW_NAME
     142having count(*) = 1
     143;
     144
    113145
    114146
     
    169201;
    170202
     203delete dtiv from DIMENSION_TO_INDICATOR_VIEW dtiv, DIMENSION_TO_INDICATOR_VIEW dtiv2
     204where dtiv.DIMENSION_USAGE != 'constant'
     205  and dtiv2.DIMENSION_USAGE = 'constant'
     206  and dtiv.INDICATOR_VIEW_NAME = dtiv2.INDICATOR_VIEW_NAME
     207  and dtiv.DIMENSION_NAME      = dtiv2.DIMENSION_NAME
     208;
     209
     210delete dtiv from DIMENSION_TO_INDICATOR_VIEW dtiv, DIMENSION_TO_INDICATOR_VIEW dtiv2
     211where dtiv.DIMENSION_USAGE != 'series'
     212  and dtiv2.DIMENSION_USAGE = 'series'
     213  and dtiv.INDICATOR_VIEW_NAME = dtiv2.INDICATOR_VIEW_NAME
     214  and dtiv.DIMENSION_NAME      = dtiv2.DIMENSION_NAME
     215;
     216
    171217
    172218
Note: See TracChangeset for help on using the changeset viewer.