Changeset 25223 in main

Timestamp:

05/11/22 16:36:41 (17 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:

• 1_data-cleanup.sql (modified) (2 diffs)
• 7_data-ivv_dimensions.sql (modified) (1 diff)

trunk/db/src/main/scripts/mysql/migrate/1_data-cleanup.sql

@@ -67 +67 @@
 ;
 
+
 /* CONCISE_TITLE: set the new v3 ip.CONCISE_TITLE column
    IMPORTANT: MUST BE RAN BEFORE SETTING IV.TITLE.
- */
+Kim wanted it turned off for NJ and UT 5/11/2022
+
 update INDICATOR i set CONCISE_TITLE = ifnull(
         (select substring(TITLE, 1, 100) 
@@ -79 +81 @@
         , '')
 ;
+ */
+
 
 /* DEMOGRAPHICS: set the indicator demographic flag */

trunk/db/src/main/scripts/mysql/migrate/7_data-ivv_dimensions.sql

@@ -211 +211 @@
 
 
+/* Update any null IVV series dims to the constant where the temp constant IVN
+        matches IVV IVN and the IVV series dim is null and the temp constant is not
+        already set for the period or dim1.
+
+        RULE: if constant is set in the mapping and is not used as dim1 or period 
+        dim and the ivv dim_2 is null then set the series dim as the constant.
+
+        example view name: AgeSpecBirthRate.REYear1517
+*/
+select * from TMP_IPV_CONSTANT_DIM_NAME t
+where exists
+(
+        select 1
+        from INDICATOR_VIEW_VALUE ivv
+        where t.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
+      and DIMENSION_2_NAME is null
+          and DIMENSION_2_NAME is null
+          and t.DIMENSION_NAME != PERIOD_DIMENSION_NAME
+          and t.DIMENSION_NAME != DIMENSION_1_NAME
+)
+;
+
+select *
+from INDICATOR_VIEW_VALUE ivv
+where DIMENSION_2_NAME is null
+  and DIMENSION_2_NAME is null
+  and exists(
+                select 1 from TMP_IPV_CONSTANT_DIM_NAME t
+                where t.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
+                  and t.DIMENSION_NAME != PERIOD_DIMENSION_NAME
+                  and t.DIMENSION_NAME != DIMENSION_1_NAME
+                )
+;
+
+update INDICATOR_VIEW_VALUE ivv
+        inner join 
+        (select DIMENSION_NAME, DIMENSION_VALUE, INDICATOR_VIEW_NAME
+         from TMP_IPV_CONSTANT_DIM_NAME 
+        ) t
+        on 
+        (
+                t.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
+                and t.DIMENSION_NAME != ivv.PERIOD_DIMENSION_NAME
+                and t.DIMENSION_NAME != ivv.DIMENSION_1_NAME
+        )
+set DIMENSION_2_NAME  = t.DIMENSION_NAME,
+        DIMENSION_2_VALUE = t.DIMENSION_VALUE
+where ivv.DIMENSION_2_NAME  is null
+  and ivv.DIMENSION_2_VALUE is null
+;
+
+
+
 /* spot test */
 select * from INDICATOR_VIEW_VALUE where DIMENSION_1_NAME is null or DIMENSION_1_VALUE is null;