Ignore:
Timestamp:
04/07/17 00:32:11 (3 years ago)
Author:
Garth Braithwaite
Message:

db, java, xslt - cleaned up the dims to iv sql. Based inserts on actual IVV records because had duplicates. Also based on active records which does not quite line up with production NM data but it appears to check out.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/db/src/main/scripts/mysql/data-dimensions_to_iv.sql

    r13080 r13530  
    66
    77delete from DIMENSION_TO_INDICATOR_VIEW;
    8 delete from DIMENSION_VALUE_TO_IND_VIEW;
    9 
    10 
    11 /* period dim names.  A view can only have 1 period dimension */
     8
     9select count(*) from INDICATOR_VIEW;
     10select count(*) from INDICATOR_VIEW where ACTIVE_FLAG = 'x';
     11select count(*) from INDICATOR_VIEW where not exists (select 1 from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME);
     12select count(*) from INDICATOR_VIEW where ACTIVE_FLAG = 'x' and not exists (select 1 from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME);
     13select count(*) from INDICATOR_VIEW where ACTIVE_FLAG = 'x' and not exists (select 1 from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME and ACTIVE_FLAG = 'x');
     14
     15/*
     161203 views
     171153 active views
     18126 views that don't have values  ==> 1203 - 126 = 1087
     19126 active views that don't have any values
     20126 active views that don't have active values ==> 1153-126 = 1027
     21
     221077 period records inserted. 126 don't have a period associated with it.
     23cat records inserted.
     24series records inserted.
     25*/
     26
     27/* period dim names.  Had a bunch of other checks and inserts but this one gets
     28        all - as long as the IVV records have been populated correctly.  See commits
     29        prior to 4/6/2017 for other versions.
     30*/
    1231insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    1332select ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, 'period'
     
    2241;
    2342
    24 
    25 /* Test
    26 1247 total
    27 1101 are active
    28 1039 have period
    29 
    30 so the 208 that do not have a period appear to be new and have no values or are
    31 inactive.
    32 */
    33 select *
     43/* Test that all views have a period dimension.  They're all active but no IVVs... */
     44select NAME, ACTIVE_FLAG
     45from INDICATOR_VIEW iv
     46where not exists
     47        (select 1
     48         from DIMENSION_TO_INDICATOR_VIEW dtiv
     49         where dtiv.DIMENSION_USAGE = 'period'
     50           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
     51        )
     52;
     53
     54/* set a default to years combined for those IVs that don't have any IVV records to draw from. */
     55insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
     56select NAME, 'YearsCombined', 'period'
     57from INDICATOR_VIEW iv
     58where not exists
     59        (select 1
     60         from DIMENSION_TO_INDICATOR_VIEW dtiv
     61         where dtiv.DIMENSION_USAGE = 'period'
     62           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
     63        )
     64;
     65
     66
     67
     68/* Do the same thing for categories.  1870 rows created. */
     69insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
     70select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, 'category'
    3471from INDICATOR_VIEW_VALUE ivv
    3572where not exists
    3673        (select dtiv.INDICATOR_VIEW_NAME
    3774         from DIMENSION_TO_INDICATOR_VIEW dtiv
    38          where dtiv.DIMENSION_USAGE = 'period'
     75         where dtiv.DIMENSION_USAGE = 'category'
    3976           and dtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
    4077        )
    41 group by ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME
    42 ;
    43 
    44 
    45 
    46 /* add in the IV series and cat dimension IF NOT NM%.  This was split out so the
    47         NM check is not really not needed as the SERIES_DIM and CAT_DIM are already
    48         mapped properly to the DS_SERIES and DS_CAT NAMEs.  This code also handles 
    49         setting up the approp period series and cat dim relationships. 
    50         *** Just need to handle NM% different as it is split. ***
    51 */
    52 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    53 select iv.NAME, iv.SERIES_DIMENSION_NAME, 'series'
    54 from INDICATOR_VIEW iv
    55 where iv.DATASET_SERIES_NAME not like 'NM%'
    56   and not exists
     78group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME
     79;
     80
     81select NAME, ACTIVE_FLAG, CATEGORY_DIMENSION_NAME
     82from INDICATOR_VIEW iv
     83where not exists
     84        (select 1
     85         from DIMENSION_TO_INDICATOR_VIEW dtiv
     86         where dtiv.DIMENSION_USAGE = 'category'
     87           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
     88        )
     89;
     90
     91insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
     92select NAME, CATEGORY_DIMENSION_NAME, 'category'
     93from INDICATOR_VIEW iv
     94where not exists
     95        (select 1
     96         from DIMENSION_TO_INDICATOR_VIEW dtiv
     97         where dtiv.DIMENSION_USAGE = 'category'
     98           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
     99        )
     100;
     101
     102
     103
     104/* Do the same thing for series 1122 rows */
     105insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
     106select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, 'series'
     107from INDICATOR_VIEW_VALUE ivv
     108where not exists
    57109        (select dtiv.INDICATOR_VIEW_NAME
    58110         from DIMENSION_TO_INDICATOR_VIEW dtiv
    59111         where dtiv.DIMENSION_USAGE = 'series'
    60            and dtiv.INDICATOR_VIEW_NAME = iv.NAME
    61            and dtiv.DIMENSION_NAME  = iv.SERIES_DIMENSION_NAME
    62         )
    63 ;
    64 
    65 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    66 select iv.NAME, iv.CATEGORY_DIMENSION_NAME, 'category' from INDICATOR_VIEW iv
    67 where iv.DATASET_CATEGORY_NAME not like 'NM%'
    68   and not exists
    69         (select dtiv.INDICATOR_VIEW_NAME
    70          from DIMENSION_TO_INDICATOR_VIEW dtiv
    71          where dtiv.DIMENSION_USAGE = 'category'
    72            and dtiv.INDICATOR_VIEW_NAME = iv.NAME
    73            and dtiv.DIMENSION_NAME  = iv.CATEGORY_DIMENSION_NAME
    74         )
    75 ;
    76 
    77 
    78 /* Add the split apart NM_% series that were split into US/State/TX. */
    79 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    80 select iv.NAME, 'State', 'series'
    81 from INDICATOR_VIEW iv
    82 where iv.DATASET_SERIES_NAME like 'NM%'
    83   and not exists
    84         (select dtiv.INDICATOR_VIEW_NAME
    85          from DIMENSION_TO_INDICATOR_VIEW dtiv
    86          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    87            and dtiv.DIMENSION_NAME  = 'State'
    88            and dtiv.DIMENSION_USAGE = 'series'
    89         )
    90 ;
    91 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    92 select iv.NAME, 'US', 'series'
    93 from INDICATOR_VIEW iv
    94 where iv.DATASET_SERIES_NAME like 'NM%'
    95   and not exists
    96         (select dtiv.INDICATOR_VIEW_NAME
    97          from DIMENSION_TO_INDICATOR_VIEW dtiv
    98          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    99            and dtiv.DIMENSION_NAME  = 'US'
    100            and dtiv.DIMENSION_USAGE = 'series'
    101         )
    102 ;
    103 
    104 
    105 
    106 /* add the State and US dimension for Geo's and age groups.  Do for category and
    107         series.
    108 */
    109 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    110 select iv.NAME, 'State', 'category'
    111 from INDICATOR_VIEW iv
    112 where ((iv.CATEGORY_DIMENSION_NAME like 'Geo%')
    113                 or (iv.CATEGORY_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11',
    114                                 'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult',
    115                                 'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH',
    116                                 'AgeGrp8Injury', 'AgeGrp8MomAge'
    117                         ))
    118            )
    119   and not exists
    120         (select dtiv.INDICATOR_VIEW_NAME
    121          from DIMENSION_TO_INDICATOR_VIEW dtiv
    122          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    123            and dtiv.DIMENSION_NAME  = 'State'
    124            and dtiv.DIMENSION_USAGE = 'category'
    125         )
    126 ;
    127 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    128 select iv.NAME, 'US', 'category'
    129 from INDICATOR_VIEW iv
    130 where ((iv.CATEGORY_DIMENSION_NAME like 'Geo%')
    131                 or (iv.CATEGORY_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11',
    132                                 'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult',
    133                                 'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH',
    134                                 'AgeGrp8Injury', 'AgeGrp8MomAge'
    135                         ))
    136            )
    137   and not exists
    138         (select dtiv.INDICATOR_VIEW_NAME
    139          from DIMENSION_TO_INDICATOR_VIEW dtiv
    140          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    141            and dtiv.DIMENSION_NAME  = 'US'
    142            and dtiv.DIMENSION_USAGE = 'category'
    143         )
    144 ;
    145 
    146 
    147 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    148 select iv.NAME, 'State', 'series'
    149 from INDICATOR_VIEW iv
    150 where ((iv.SERIES_DIMENSION_NAME like 'Geo%')
    151                 or (iv.SERIES_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11',
    152                                 'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult',
    153                                 'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH',
    154                                 'AgeGrp8Injury', 'AgeGrp8MomAge'
    155                         ))
    156            )
    157   and not exists
    158         (select dtiv.INDICATOR_VIEW_NAME
    159          from DIMENSION_TO_INDICATOR_VIEW dtiv
    160          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    161            and dtiv.DIMENSION_NAME  = 'State'
    162            and dtiv.DIMENSION_USAGE = 'series'
    163         )
    164 ;
    165 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    166 select iv.NAME, 'US', 'series'
    167 from INDICATOR_VIEW iv
    168 where ((iv.SERIES_DIMENSION_NAME like 'Geo%')
    169                 or (iv.SERIES_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11',
    170                                 'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult',
    171                                 'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH',
    172                                 'AgeGrp8Injury', 'AgeGrp8MomAge'
    173                         ))
    174            )
    175   and not exists
    176         (select dtiv.INDICATOR_VIEW_NAME
    177          from DIMENSION_TO_INDICATOR_VIEW dtiv
    178          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    179            and dtiv.DIMENSION_NAME  = 'US'
    180            and dtiv.DIMENSION_USAGE = 'series'
    181         )
    182 ;
    183 
    184 
    185 
    186 
    187 /* set the geocnty if NM_Cnty_US */
    188 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    189 select iv.NAME, 'GeoCnty', 'series'
    190 from INDICATOR_VIEW iv
    191 where iv.DATASET_SERIES_NAME = 'NM_Cnty_US'
    192   and not exists
    193         (select dtiv.INDICATOR_VIEW_NAME
    194          from DIMENSION_TO_INDICATOR_VIEW dtiv
    195          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    196            and dtiv.DIMENSION_NAME  = 'GeoCnty'
    197            and dtiv.DIMENSION_USAGE = 'series'
    198         )
    199 ;
    200 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    201 select iv.NAME, 'GeoCnty', 'category'
    202 from INDICATOR_VIEW iv
    203 where iv.DATASET_CATEGORY_NAME = 'NM_Cnty_US'
    204   and not exists
    205         (select dtiv.INDICATOR_VIEW_NAME
    206          from DIMENSION_TO_INDICATOR_VIEW dtiv
    207          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    208            and dtiv.DIMENSION_NAME  = 'GeoCnty'
    209            and dtiv.DIMENSION_USAGE = 'category'
    210         )
    211 ;
    212 
    213 
    214 
    215 /* checks */
    216 select NAME, SERIES_DIMENSION_NAME, CATEGORY_DIMENSION_NAME
    217 from INDICATOR_VIEW
    218 where ACTIVE_FLAG = 'x'
    219   and NAME not in (select distinct INDICATOR_VIEW_NAME from DIMENSION_TO_INDICATOR_VIEW)
    220 ;
    221 select NAME, SERIES_DIMENSION_NAME, CATEGORY_DIMENSION_NAME
    222 from INDICATOR_VIEW
    223 where SERIES_DIMENSION_NAME is null or CATEGORY_DIMENSION_NAME is null
    224 ;
    225 
    226 
    227 select 'Missing Series', NAME, DATASET_CATEGORY_NAME, CATEGORY_DIMENSION_NAME, SERIES_DIMENSION_NAME, DATASET_SERIES_NAME
    228 from INDICATOR_VIEW iv
    229 where not exists
    230         (select dtiv.INDICATOR_VIEW_NAME
    231          from DIMENSION_TO_INDICATOR_VIEW dtiv
    232          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    233            and dtiv.DIMENSION_USAGE = 'series'
    234         )
    235 ;
    236 select 'Missing Cat', NAME, DATASET_CATEGORY_NAME, CATEGORY_DIMENSION_NAME, SERIES_DIMENSION_NAME, DATASET_SERIES_NAME
    237 from INDICATOR_VIEW iv
    238 where not exists
    239         (select dtiv.INDICATOR_VIEW_NAME
    240          from DIMENSION_TO_INDICATOR_VIEW dtiv
    241          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    242            and dtiv.DIMENSION_USAGE = 'category'
    243         )
    244 ;
    245 select 'Missing Period', NAME, DATASET_CATEGORY_NAME, CATEGORY_DIMENSION_NAME, SERIES_DIMENSION_NAME, DATASET_SERIES_NAME
    246 from INDICATOR_VIEW iv
    247 where not exists
    248         (select dtiv.INDICATOR_VIEW_NAME
    249          from DIMENSION_TO_INDICATOR_VIEW dtiv
    250          where dtiv.INDICATOR_VIEW_NAME = iv.NAME
    251            and dtiv.DIMENSION_USAGE = 'period'
    252         )
    253 ;
    254 
    255 
    256 
    257 /*
    258 ======================== have an issue with a bunch not having period set - see
    259 inactive and new IPs that do not appear to have values at this point...
    260 */
    261 
    262 
    263 
    264 /* catch the DIMENSION_TO_INDICATOR_VIEW that do NOT have a period dimension
    265         set yet.  First try category dim name being a period and use that.  Next try
    266         the series.  If still nothing then default to the YearsCombined.
    267 */
    268 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    269 select NAME, CATEGORY_DIMENSION_NAME, 'period'
    270 from INDICATOR_VIEW iv
    271 where not exists
    272         (select dtiv.INDICATOR_VIEW_NAME
    273          from DIMENSION_TO_INDICATOR_VIEW dtiv
    274          where dtiv.DIMENSION_USAGE = 'period'
    275            and dtiv.INDICATOR_VIEW_NAME = iv.NAME
    276         )
    277   and iv.CATEGORY_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG = 'x')
    278 ;
    279 
    280 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    281 select NAME, SERIES_DIMENSION_NAME, 'period'
    282 from INDICATOR_VIEW iv
    283 where not exists
    284         (select dtiv.INDICATOR_VIEW_NAME
    285          from DIMENSION_TO_INDICATOR_VIEW dtiv
    286          where dtiv.DIMENSION_USAGE = 'period'
    287            and dtiv.INDICATOR_VIEW_NAME = iv.NAME
    288         )
    289   and iv.SERIES_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG = 'x')
    290 ;
    291 
    292 insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
    293 select NAME, 'YearsCombined', 'period'
    294 from INDICATOR_VIEW iv
    295 where not exists
    296         (select dtiv.INDICATOR_VIEW_NAME
    297          from DIMENSION_TO_INDICATOR_VIEW dtiv
    298          where dtiv.DIMENSION_USAGE = 'period'
    299            and dtiv.INDICATOR_VIEW_NAME = iv.NAME
    300         )
    301 ;
    302 
    303 
    304 
    305 /* quick test to show which IVs have multiple period usages */
    306 select IP_NAME
    307 from (
    308                 select substring_index(INDICATOR_VIEW_NAME, '.', 1) as IP_NAME, DIMENSION_NAME, count(*) as GB_COUNT
    309                 from DIMENSION_TO_INDICATOR_VIEW
    310                 where DIMENSION_USAGE = 'period'
    311                 group by IP_NAME, DIMENSION_NAME
    312         having count(*) > 1
    313     ) xxx
    314 group by IP_NAME
    315 having count(IP_NAME) > 1
    316 order by 1
    317 ;
    318 
    319 /* show basic list of IP's and count of the distinct period DIMs */
    320 select substring_index(INDICATOR_VIEW_NAME, '.', 1) as IP_NAME, DIMENSION_NAME, count(*) as GB_COUNT
     112           and dtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     113        )
     114group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME
     115;
     116
     117select NAME, ACTIVE_FLAG, SERIES_DIMENSION_NAME
     118from INDICATOR_VIEW iv
     119where not exists
     120        (select 1
     121         from DIMENSION_TO_INDICATOR_VIEW dtiv
     122         where dtiv.DIMENSION_USAGE = 'series'
     123           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
     124        )
     125;
     126
     127insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
     128select NAME, SERIES_DIMENSION_NAME, 'series'
     129from INDICATOR_VIEW iv
     130where not exists
     131        (select 1
     132         from DIMENSION_TO_INDICATOR_VIEW dtiv
     133         where dtiv.DIMENSION_USAGE = 'series'
     134           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
     135        )
     136;
     137
     138
     139
     140/* Do some cleanup on the DIMENSION_NAMEs that are NOT dimension names.  This can
     141        get crazy but since all the values are for views that didn't have any values
     142        simply set series to NONE.
     143*/
     144update DIMENSION_TO_INDICATOR_VIEW set DIMENSION_NAME = 'None'
     145where  DIMENSION_USAGE = 'series'
     146  and  DIMENSION_NAME not in (select NAME from DIMENSION)
     147;
     148
     149/* quick test to make sure they're all covered */
     150select * from DIMENSION_TO_INDICATOR_VIEW
     151where DIMENSION_NAME not in (select NAME from DIMENSION)
     152;
     153
     154/* Quick test to show which IVs have multiple period usages.  This is
     155        technically possible and might be acceptable but for IVV this really should
     156        never be the case.  This typically happens with orphaned IVV records.
     157*/
     158select INDICATOR_VIEW_NAME, DIMENSION_NAME, count(*) as MULT_PERIOD_COUNT
    321159from DIMENSION_TO_INDICATOR_VIEW
    322160where DIMENSION_USAGE = 'period'
    323 group by IP_NAME, DIMENSION_NAME
     161group by INDICATOR_VIEW_NAME, DIMENSION_NAME
    324162having count(*) > 1
    325 order by 1
    326 ;
    327 
    328 select *
    329 from DIMENSION_TO_INDICATOR_VIEW
    330 where DIMENSION_USAGE = 'period'
    331   and substring_index(INDICATOR_VIEW_NAME, '.', 1) = 'DiabHosp'
    332 ;
     163;
     164
     165
    333166
    334167
     
    342175*/
    343176
    344 
    345 
     177delete from DIMENSION_VALUE_TO_IND_VIEW;
    346178
    347179
     
    350182select ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
    351183from INDICATOR_VIEW_VALUE ivv
    352 where not exists
     184where ACTIVE_FLAG = 'x'
     185  and not exists
    353186        (select 1
    354187         from DIMENSION_VALUE_TO_IND_VIEW dviv
     
    363196select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE
    364197from INDICATOR_VIEW_VALUE ivv
    365 where not exists
     198where ACTIVE_FLAG = 'x'
     199  and not exists
    366200        (select 1
    367201         from DIMENSION_VALUE_TO_IND_VIEW dviv
     
    376210select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE
    377211from INDICATOR_VIEW_VALUE ivv
    378 where not exists
     212where ACTIVE_FLAG = 'x'
     213  and not exists
    379214        (select 1
    380215         from DIMENSION_VALUE_TO_IND_VIEW dviv
     
    386221;
    387222
     223
     224
     225/* do some cleanup - get rid of any dim values that are not dim values.
     226        Appears that most are series that have a State and/or US which was removed.
     227        Example RacEthDOH has both state and US which are now split out.
     228*/
     229select * from DIMENSION_VALUE_TO_IND_VIEW
     230where not exists
     231        (select 1
     232         from DIMENSION_VALUE dv
     233         where dv.ACTIVE_FLAG = 'x'
     234           and dv.VALUE          = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_VALUE
     235           and dv.DIMENSION_NAME = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_NAME
     236        )
     237order by INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE
     238;
     239
     240delete from DIMENSION_VALUE_TO_IND_VIEW
     241where not exists
     242        (select 1
     243         from DIMENSION_VALUE dv
     244         where dv.ACTIVE_FLAG = 'x'
     245           and dv.VALUE          = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_VALUE
     246           and dv.DIMENSION_NAME = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_NAME
     247        )
     248;
    388249
    389250
Note: See TracChangeset for help on using the changeset viewer.