source: main/trunk/db/src/main/scripts/mysql/migrate/9_data-iv_dimension_value.sql @ 25198

Last change on this file since 25198 was 25198, checked in by GarthBraithwaite_STG, 3 months ago

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 size: 4.4 KB
Line 
1/* Sets the DIMENSION_VALUE_TO_IND_VIEW. 
2        This script operates off of the IV and IVV tables matching series and cats
3        with the TMP_DSS23_DIM, DSC, and TMP_IPV_CONSTANT_DIM_NAME mapping tables.
4
5        NOTE: The IV table does NOT account for the splitting out of State and US
6        and so the TMP DSC/DSS and IVV.DIM1 and DIM2 are used to determine the
7        correct DIM and VALUE rows for the given IPV's DIMs and VALUES. 
8*/
9
10/* check problem records */
11select *
12from INDICATOR_VIEW_VALUE ivv
13where ACTIVE_FLAG = 'x'
14  and ivv.DIMENSION_1_NAME  = ivv.DIMENSION_2_NAME
15  and ivv.DIMENSION_1_VALUE = ivv.DIMENSION_2_VALUE
16;
17
18
19delete from DIMENSION_VALUE_TO_IND_VIEW;
20
21/* Populate the indicator view dimension values based on the ivv records.  */
22insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
23select ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
24from INDICATOR_VIEW_VALUE ivv
25where ACTIVE_FLAG = 'x'
26  and ivv.PERIOD_DIMENSION_NAME  is not null
27  and ivv.PERIOD_DIMENSION_VALUE is not null
28group by ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
29;
30
31/* similar for IVV Dim 1 */
32insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
33select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE
34from INDICATOR_VIEW_VALUE ivv
35where ACTIVE_FLAG = 'x'
36  and ivv.DIMENSION_1_NAME  is not null
37  and ivv.DIMENSION_1_VALUE is not null
38group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE
39;
40
41/* lastly for IVV Dim 2 */
42insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
43select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE
44from INDICATOR_VIEW_VALUE ivv
45where ACTIVE_FLAG = 'x'
46  and ivv.DIMENSION_2_NAME  is not null
47  and ivv.DIMENSION_2_VALUE is not null
48group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE
49;
50
51/* code to check if exists as dim1 and dim 2.  This isn't needed as the dims
52        in IVV should already be correct.  If not can always run this against the
53        Dim To Ind VIew intersection table that also has the correct usages.
54        Doing both ways results in the same records being inserted.
55  and not exists
56        (select 1 from DIMENSION_VALUE_TO_IND_VIEW dvtiv
57         where dvtiv.DIMENSION_NAME  = ivv.DIMENSION_2_NAME
58           and dvtiv.DIMENSION_VALUE = ivv.DIMENSION_2_VALUE
59           and dvtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
60        )
61*/
62
63
64
65/* set the default sort order based on the dimension value sort order. */
66update DIMENSION_VALUE_TO_IND_VIEW dvtiv
67        set dvtiv.SORT_ORDER = 
68    (select dv.SORT_ORDER
69         from DIMENSION_VALUE dv
70         where dv.DIMENSION_NAME = dvtiv.DIMENSION_NAME
71           and dv.VALUE = dvtiv.DIMENSION_VALUE
72        )
73where dvtiv.SORT_ORDER is null
74;
75
76
77
78/* See if there are any IV dim values that are not active dim values.  Most are
79        inactive dim values.  Thought about leaving in so could capture those potential
80        data points but thinking about it they should be removed and readded if needed.
81        Lois also said to delete them.
82*/
83select * from DIMENSION_VALUE_TO_IND_VIEW
84where not exists
85        (select 1 
86         from DIMENSION_VALUE dv
87         where dv.ACTIVE_FLAG = 'x'
88           and dv.VALUE          = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_VALUE
89           and dv.DIMENSION_NAME = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_NAME
90        )
91order by INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE
92;
93/* kim said to leave these
94delete from DIMENSION_VALUE_TO_IND_VIEW
95where not exists
96        (select 1
97         from DIMENSION_VALUE dv
98         where dv.ACTIVE_FLAG = 'x'
99           and dv.VALUE          = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_VALUE
100           and dv.DIMENSION_NAME = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_NAME
101        )
102;
103*/
104
105
106/* test to make sure that every view has dim values */
107select INDICATOR_VIEW_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME, PERIOD_DIMENSION_NAME
108from INDICATOR_VIEW_VALUE
109where ACTIVE_FLAG = 'x'
110  and INDICATOR_VIEW_NAME not in (select distinct INDICATOR_VIEW_NAME from DIMENSION_VALUE_TO_IND_VIEW)
111;
112
113
114/* spot test */
115select * from DIMENSION_VALUE_TO_IND_VIEW where INDICATOR_VIEW_NAME
116in ('AgeSpecBirthRate.REYear1517', 'AlzDeath.RESex', 'EPHTNeonatalMort.Cause',
117'Formaldehyde.County', 'GeneralHealth.Year', 'TuberTherapy.Year', 'UnintInjDeath.CODRE'
118);
119
120/*------------------------------ END OF FILE ------------------------------*/
Note: See TracBrowser for help on using the repository browser.