Changeset 25223 in main


Ignore:
Timestamp:
05/11/22 16:36:41 (5 months ago)
Author:
GarthBraithwaite_STG
Message:

db - removed the default setting of IP concise title so it will be blank as per Kim. Added IVV series dimension constant processing if const mapping matches and ivv series is null.

Location:
trunk/db/src/main/scripts/mysql/migrate
Files:
2 edited

Legend:

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

    r25198 r25223  
    6767;
    6868
     69
    6970/* CONCISE_TITLE: set the new v3 ip.CONCISE_TITLE column
    7071   IMPORTANT: MUST BE RAN BEFORE SETTING IV.TITLE.
    71  */
     72Kim wanted it turned off for NJ and UT 5/11/2022
     73
    7274update INDICATOR i set CONCISE_TITLE = ifnull(
    7375        (select substring(TITLE, 1, 100)
     
    7981        , '')
    8082;
     83 */
     84
    8185
    8286/* DEMOGRAPHICS: set the indicator demographic flag */
  • trunk/db/src/main/scripts/mysql/migrate/7_data-ivv_dimensions.sql

    r25095 r25223  
    211211
    212212
     213/* Update any null IVV series dims to the constant where the temp constant IVN
     214        matches IVV IVN and the IVV series dim is null and the temp constant is not
     215        already set for the period or dim1.
     216
     217        RULE: if constant is set in the mapping and is not used as dim1 or period
     218        dim and the ivv dim_2 is null then set the series dim as the constant.
     219
     220        example view name: AgeSpecBirthRate.REYear1517
     221*/
     222select * from TMP_IPV_CONSTANT_DIM_NAME t
     223where exists
     224(
     225        select 1
     226        from INDICATOR_VIEW_VALUE ivv
     227        where t.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     228      and DIMENSION_2_NAME is null
     229          and DIMENSION_2_NAME is null
     230          and t.DIMENSION_NAME != PERIOD_DIMENSION_NAME
     231          and t.DIMENSION_NAME != DIMENSION_1_NAME
     232)
     233;
     234
     235select *
     236from INDICATOR_VIEW_VALUE ivv
     237where DIMENSION_2_NAME is null
     238  and DIMENSION_2_NAME is null
     239  and exists(
     240                select 1 from TMP_IPV_CONSTANT_DIM_NAME t
     241                where t.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     242                  and t.DIMENSION_NAME != PERIOD_DIMENSION_NAME
     243                  and t.DIMENSION_NAME != DIMENSION_1_NAME
     244                )
     245;
     246
     247update INDICATOR_VIEW_VALUE ivv
     248        inner join
     249        (select DIMENSION_NAME, DIMENSION_VALUE, INDICATOR_VIEW_NAME
     250         from TMP_IPV_CONSTANT_DIM_NAME
     251        ) t
     252        on
     253        (
     254                t.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     255                and t.DIMENSION_NAME != ivv.PERIOD_DIMENSION_NAME
     256                and t.DIMENSION_NAME != ivv.DIMENSION_1_NAME
     257        )
     258set DIMENSION_2_NAME  = t.DIMENSION_NAME,
     259        DIMENSION_2_VALUE = t.DIMENSION_VALUE
     260where ivv.DIMENSION_2_NAME  is null
     261  and ivv.DIMENSION_2_VALUE is null
     262;
     263
     264
     265
    213266/* spot test */
    214267select * from INDICATOR_VIEW_VALUE where DIMENSION_1_NAME is null or DIMENSION_1_VALUE is null;
Note: See TracChangeset for help on using the changeset viewer.