ACD_METRICS_OVERVIEW_V
Details
-
Schema: FUSION
-
Object owner: ACD
-
Object type: VIEW
Columns
Name |
---|
METRICS_OVERVIEW_ID CONCEPT_STRUCTURE_ID METRIC_TYPE METRIC_TARGET METRIC_VALUE0 METRIC_VALUE1 METRIC_VALUE2 METRIC_VALUE3 METRIC_VALUE4 METRIC_VALUE5 METRIC_VALUE6 METRIC_VALUE7 METRIC_VALUE8 METRIC_VALUE9 METRIC_VALUE10 METRIC_VALUE11 METRIC_VALUE12 METRIC_VALUE13 METRIC_VALUE14 METRIC_VALUE15 METRIC_INCOMPLETE0 METRIC_INCOMPLETE1 METRIC_INCOMPLETE2 METRIC_INCOMPLETE3 METRIC_INCOMPLETE4 METRIC_INCOMPLETE5 METRIC_INCOMPLETE6 METRIC_INCOMPLETE7 METRIC_INCOMPLETE8 METRIC_INCOMPLETE9 METRIC_INCOMPLETE10 METRIC_INCOMPLETE11 METRIC_INCOMPLETE12 METRIC_INCOMPLETE13 METRIC_INCOMPLETE14 METRIC_INCOMPLETE15 METRIC_VARIANCE0 METRIC_VARIANCE1 METRIC_VARIANCE2 METRIC_VARIANCE3 METRIC_VARIANCE4 METRIC_VARIANCE5 METRIC_VARIANCE6 METRIC_VARIANCE7 METRIC_VARIANCE8 METRIC_VARIANCE9 METRIC_VARIANCE10 METRIC_VARIANCE11 METRIC_VARIANCE12 METRIC_VARIANCE13 METRIC_VARIANCE14 METRIC_VARIANCE15 METRIC_VALUE_NORMALIZED0 METRIC_VALUE_NORMALIZED1 METRIC_VALUE_NORMALIZED2 METRIC_VALUE_NORMALIZED3 METRIC_VALUE_NORMALIZED4 METRIC_VALUE_NORMALIZED5 METRIC_VALUE_NORMALIZED6 METRIC_VALUE_NORMALIZED7 METRIC_VALUE_NORMALIZED8 METRIC_VALUE_NORMALIZED9 METRIC_VALUE_NORMALIZED10 METRIC_VALUE_NORMALIZED11 METRIC_VALUE_NORMALIZED12 METRIC_VALUE_NORMALIZED13 METRIC_VALUE_NORMALIZED14 METRIC_VALUE_NORMALIZED15 METRIC_TARGET_NORMALIZED |
Query
SQL_Statement |
---|
SELECT m0.METRICS_ID AS METRICS_OVERVIEW_ID, s.concept_structure_id AS CONCEPT_STRUCTURE_ID ,'COST' AS METRIC_TYPE ,s.target_cost AS METRIC_TARGET ,m0.cost as METRIC_VALUE0 ,m1.cost as METRIC_VALUE1 ,m2.cost as METRIC_VALUE2 ,m3.cost as METRIC_VALUE3 ,m4.cost as METRIC_VALUE4 ,m5.cost as METRIC_VALUE5 ,m6.cost as METRIC_VALUE6 ,m7.cost as METRIC_VALUE7 ,m8.cost as METRIC_VALUE8 ,m9.cost as METRIC_VALUE9 ,m10.cost as METRIC_VALUE10 ,m11.cost as METRIC_VALUE11 ,m12.cost as METRIC_VALUE12 ,m13.cost as METRIC_VALUE13 ,m14.cost as METRIC_VALUE14 ,m15.cost as METRIC_VALUE15 ,m0.cost_incomplete as METRIC_INCOMPLETE0 ,m1.cost_incomplete as METRIC_INCOMPLETE1 ,m2.cost_incomplete as METRIC_INCOMPLETE2 ,m3.cost_incomplete as METRIC_INCOMPLETE3 ,m4.cost_incomplete as METRIC_INCOMPLETE4 ,m5.cost_incomplete as METRIC_INCOMPLETE5 ,m6.cost_incomplete as METRIC_INCOMPLETE6 ,m7.cost_incomplete as METRIC_INCOMPLETE7 ,m8.cost_incomplete as METRIC_INCOMPLETE8 ,m9.cost_incomplete as METRIC_INCOMPLETE9 ,m10.cost_incomplete as METRIC_INCOMPLETE10 ,m11.cost_incomplete as METRIC_INCOMPLETE11 ,m12.cost_incomplete as METRIC_INCOMPLETE12 ,m13.cost_incomplete as METRIC_INCOMPLETE13 ,m14.cost_incomplete as METRIC_INCOMPLETE14 ,m15.cost_incomplete as METRIC_INCOMPLETE15 ,m0.cost_variance as METRIC_VARIANCE0 ,m1.cost_variance as METRIC_VARIANCE1 ,m2.cost_variance as METRIC_VARIANCE2 ,m3.cost_variance as METRIC_VARIANCE3 ,m4.cost_variance as METRIC_VARIANCE4 ,m5.cost_variance as METRIC_VARIANCE5 ,m6.cost_variance as METRIC_VARIANCE6 ,m7.cost_variance as METRIC_VARIANCE7 ,m8.cost_variance as METRIC_VARIANCE8 ,m9.cost_variance as METRIC_VARIANCE9 ,m10.cost_variance as METRIC_VARIANCE10 ,m11.cost_variance as METRIC_VARIANCE11 ,m12.cost_variance as METRIC_VARIANCE12 ,m13.cost_variance as METRIC_VARIANCE13 ,m14.cost_variance as METRIC_VARIANCE14 ,m15.cost_variance as METRIC_VARIANCE15 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m0.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED0 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m1.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED1 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m2.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED2 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m3.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED3 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m4.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED4 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m5.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED5 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m6.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED6 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m7.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED7 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m8.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED8 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m9.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED9 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m10.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED10 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m11.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED11 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m12.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED12 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m13.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED13 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m14.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED14 ,CASE WHEN s.target_cost IS NULL THEN NULL WHEN s.target_cost = 0 THEN 0 ELSE (NVL(m15.cost, 0) - s.target_cost) * 100 / s.target_cost END as METRIC_VALUE_NORMALIZED15 ,0 as METRIC_TARGET_NORMALIZED FROM acd_concept_Structure s ,acd_metrics m0 ,acd_metrics m1 ,acd_metrics m2 ,acd_metrics m3 ,acd_metrics m4 ,acd_metrics m5 ,acd_metrics m6 ,acd_metrics m7 ,acd_metrics m8 ,acd_metrics m9 ,acd_metrics m10 ,acd_metrics m11 ,acd_metrics m12 ,acd_metrics m13 ,acd_metrics m14 ,acd_metrics m15 WHERE (s.concept_structure_id = m0.concept_structure_id(+) and m0.alternative_index=0) and (s.concept_structure_id = m1.concept_structure_id(+) and m1.alternative_index=1) and (s.concept_structure_id = m2.concept_structure_id(+) and m2.alternative_index=2) and (s.concept_structure_id = m3.concept_structure_id(+) and m3.alternative_index=3) and (s.concept_structure_id = m4.concept_structure_id(+) and m4.alternative_index=4) and (s.concept_structure_id = m5.concept_structure_id(+) and m5.alternative_index=5) and (s.concept_structure_id = m6.concept_structure_id(+) and m6.alternative_index=6) and (s.concept_structure_id = m7.concept_structure_id(+) and m7.alternative_index=7) and (s.concept_structure_id = m8.concept_structure_id(+) and m8.alternative_index=8) and (s.concept_structure_id = m9.concept_structure_id(+) and m9.alternative_index=9) and (s.concept_structure_id = m10.concept_structure_id(+) and m10.alternative_index=10) and (s.concept_structure_id = m11.concept_structure_id(+) and m11.alternative_index=11) and (s.concept_structure_id = m12.concept_structure_id(+) and m12.alternative_index=12) and (s.concept_structure_id = m13.concept_structure_id(+) and m13.alternative_index=13) and (s.concept_structure_id = m14.concept_structure_id(+) and m14.alternative_index=14) and (s.concept_structure_id = m15.concept_structure_id(+) and m15.alternative_index=15) UNION ALL SELECT m1.METRICS_ID AS METRICS_OVERVIEW_ID, s.concept_structure_id AS CONCEPT_STRUCTURE_ID ,'WEIGHT' AS METRIC_TYPE ,s.target_weight AS METRIC_TARGET ,m0.weight as METRIC_VALUE0 ,m1.weight as METRIC_VALUE1 ,m2.weight as METRIC_VALUE2 ,m3.weight as METRIC_VALUE3 ,m4.weight as METRIC_VALUE4 ,m5.weight as METRIC_VALUE5 ,m6.weight as METRIC_VALUE6 ,m7.weight as METRIC_VALUE7 ,m8.weight as METRIC_VALUE8 ,m9.weight as METRIC_VALUE9 ,m10.weight as METRIC_VALUE10 ,m11.weight as METRIC_VALUE11 ,m12.weight as METRIC_VALUE12 ,m13.weight as METRIC_VALUE13 ,m14.weight as METRIC_VALUE14 ,m15.weight as METRIC_VALUE15 ,m0.weight_incomplete as METRIC_INCOMPLETE0 ,m1.weight_incomplete as METRIC_INCOMPLETE1 ,m2.weight_incomplete as METRIC_INCOMPLETE2 ,m3.weight_incomplete as METRIC_INCOMPLETE3 ,m4.weight_incomplete as METRIC_INCOMPLETE4 ,m5.weight_incomplete as METRIC_INCOMPLETE5 ,m6.weight_incomplete as METRIC_INCOMPLETE6 ,m7.weight_incomplete as METRIC_INCOMPLETE7 ,m8.weight_incomplete as METRIC_INCOMPLETE8 ,m9.weight_incomplete as METRIC_INCOMPLETE9 ,m10.weight_incomplete as METRIC_INCOMPLETE10 ,m11.weight_incomplete as METRIC_INCOMPLETE11 ,m12.weight_incomplete as METRIC_INCOMPLETE12 ,m13.weight_incomplete as METRIC_INCOMPLETE13 ,m14.weight_incomplete as METRIC_INCOMPLETE14 ,m15.weight_incomplete as METRIC_INCOMPLETE15 ,m0.weight_variance as METRIC_VARIANCE0 ,m1.weight_variance as METRIC_VARIANCE1 ,m2.weight_variance as METRIC_VARIANCE2 ,m3.weight_variance as METRIC_VARIANCE3 ,m4.weight_variance as METRIC_VARIANCE4 ,m5.weight_variance as METRIC_VARIANCE5 ,m6.weight_variance as METRIC_VARIANCE6 ,m7.weight_variance as METRIC_VARIANCE7 ,m8.weight_variance as METRIC_VARIANCE8 ,m9.weight_variance as METRIC_VARIANCE9 ,m10.weight_variance as METRIC_VARIANCE10 ,m11.weight_variance as METRIC_VARIANCE11 ,m12.weight_variance as METRIC_VARIANCE12 ,m13.weight_variance as METRIC_VARIANCE13 ,m14.weight_variance as METRIC_VARIANCE14 ,m15.weight_variance as METRIC_VARIANCE15 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m0.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED0 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m1.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED1 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m2.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED2 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m3.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED3 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m4.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED4 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m5.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED5 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m6.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED6 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m7.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED7 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m8.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED8 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m9.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED9 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m10.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED10 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m11.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED11 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m12.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED12 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m13.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED13 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m14.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED14 ,CASE WHEN s.target_weight IS NULL THEN NULL WHEN s.target_weight = 0 THEN 0 ELSE (NVL(m15.weight, 0) - s.target_weight) * 100 / s.target_weight END as METRIC_VALUE_NORMALIZED15 ,0 as METRIC_TARGET_NORMALIZED FROM acd_concept_Structure s ,acd_metrics m0 ,acd_metrics m1 ,acd_metrics m2 ,acd_metrics m3 ,acd_metrics m4 ,acd_metrics m5 ,acd_metrics m6 ,acd_metrics m7 ,acd_metrics m8 ,acd_metrics m9 ,acd_metrics m10 ,acd_metrics m11 ,acd_metrics m12 ,acd_metrics m13 ,acd_metrics m14 ,acd_metrics m15 WHERE (s.concept_structure_id = m0.concept_structure_id(+) and m0.alternative_index=0) and (s.concept_structure_id = m1.concept_structure_id(+) and m1.alternative_index=1) and (s.concept_structure_id = m2.concept_structure_id(+) and m2.alternative_index=2) and (s.concept_structure_id = m3.concept_structure_id(+) and m3.alternative_index=3) and (s.concept_structure_id = m4.concept_structure_id(+) and m4.alternative_index=4) and (s.concept_structure_id = m5.concept_structure_id(+) and m5.alternative_index=5) and (s.concept_structure_id = m6.concept_structure_id(+) and m6.alternative_index=6) and (s.concept_structure_id = m7.concept_structure_id(+) and m7.alternative_index=7) and (s.concept_structure_id = m8.concept_structure_id(+) and m8.alternative_index=8) and (s.concept_structure_id = m9.concept_structure_id(+) and m9.alternative_index=9) and (s.concept_structure_id = m10.concept_structure_id(+) and m10.alternative_index=10) and (s.concept_structure_id = m11.concept_structure_id(+) and m11.alternative_index=11) and (s.concept_structure_id = m12.concept_structure_id(+) and m12.alternative_index=12) and (s.concept_structure_id = m13.concept_structure_id(+) and m13.alternative_index=13) and (s.concept_structure_id = m14.concept_structure_id(+) and m14.alternative_index=14) and (s.concept_structure_id = m15.concept_structure_id(+) and m15.alternative_index=15) UNION ALL SELECT m2.METRICS_ID AS METRICS_OVERVIEW_ID, s.concept_structure_id AS CONCEPT_STRUCTURE_ID ,'POWER_CONSUMPTION' AS METRIC_TYPE ,s.target_power_consumption AS METRIC_TARGET ,m0.power_consumption as METRIC_VALUE0 ,m1.power_consumption as METRIC_VALUE1 ,m2.power_consumption as METRIC_VALUE2 ,m3.power_consumption as METRIC_VALUE3 ,m4.power_consumption as METRIC_VALUE4 ,m5.power_consumption as METRIC_VALUE5 ,m6.power_consumption as METRIC_VALUE6 ,m7.power_consumption as METRIC_VALUE7 ,m8.power_consumption as METRIC_VALUE8 ,m9.power_consumption as METRIC_VALUE9 ,m10.power_consumption as METRIC_VALUE10 ,m11.power_consumption as METRIC_VALUE11 ,m12.power_consumption as METRIC_VALUE12 ,m13.power_consumption as METRIC_VALUE13 ,m14.power_consumption as METRIC_VALUE14 ,m15.power_consumption as METRIC_VALUE15 ,m0.power_incomplete as METRIC_INCOMPLETE0 ,m1.power_incomplete as METRIC_INCOMPLETE1 ,m2.power_incomplete as METRIC_INCOMPLETE2 ,m3.power_incomplete as METRIC_INCOMPLETE3 ,m4.power_incomplete as METRIC_INCOMPLETE4 ,m5.power_incomplete as METRIC_INCOMPLETE5 ,m6.power_incomplete as METRIC_INCOMPLETE6 ,m7.power_incomplete as METRIC_INCOMPLETE7 ,m8.power_incomplete as METRIC_INCOMPLETE8 ,m9.power_incomplete as METRIC_INCOMPLETE9 ,m10.power_incomplete as METRIC_INCOMPLETE10 ,m11.power_incomplete as METRIC_INCOMPLETE11 ,m12.power_incomplete as METRIC_INCOMPLETE12 ,m13.power_incomplete as METRIC_INCOMPLETE13 ,m14.power_incomplete as METRIC_INCOMPLETE14 ,m15.power_incomplete as METRIC_INCOMPLETE15 ,m0.power_consumption_variance as METRIC_VARIANCE0 ,m1.power_consumption_variance as METRIC_VARIANCE1 ,m2.power_consumption_variance as METRIC_VARIANCE2 ,m3.power_consumption_variance as METRIC_VARIANCE3 ,m4.power_consumption_variance as METRIC_VARIANCE4 ,m5.power_consumption_variance as METRIC_VARIANCE5 ,m6.power_consumption_variance as METRIC_VARIANCE6 ,m7.power_consumption_variance as METRIC_VARIANCE7 ,m8.power_consumption_variance as METRIC_VARIANCE8 ,m9.power_consumption_variance as METRIC_VARIANCE9 ,m10.power_consumption_variance as METRIC_VARIANCE10 ,m11.power_consumption_variance as METRIC_VARIANCE11 ,m12.power_consumption_variance as METRIC_VARIANCE12 ,m13.power_consumption_variance as METRIC_VARIANCE13 ,m14.power_consumption_variance as METRIC_VARIANCE14 ,m15.power_consumption_variance as METRIC_VARIANCE15 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m0.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED0 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m1.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED1 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m2.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED2 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m3.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED3 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m4.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED4 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m5.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED5 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m6.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED6 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m7.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED7 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m8.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED8 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m9.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED9 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m10.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED10 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m11.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED11 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m12.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED12 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m13.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED13 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m14.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED14 ,CASE WHEN s.target_power_consumption IS NULL THEN NULL WHEN s.target_power_consumption = 0 THEN 0 ELSE (NVL(m15.power_consumption, 0) - s.target_power_consumption) * 100 / s.target_power_consumption END as METRIC_VALUE_NORMALIZED15 ,0 as METRIC_TARGET_NORMALIZED FROM acd_concept_Structure s ,acd_metrics m0 ,acd_metrics m1 ,acd_metrics m2 ,acd_metrics m3 ,acd_metrics m4 ,acd_metrics m5 ,acd_metrics m6 ,acd_metrics m7 ,acd_metrics m8 ,acd_metrics m9 ,acd_metrics m10 ,acd_metrics m11 ,acd_metrics m12 ,acd_metrics m13 ,acd_metrics m14 ,acd_metrics m15 WHERE (s.concept_structure_id = m0.concept_structure_id(+) and m0.alternative_index=0) and (s.concept_structure_id = m1.concept_structure_id(+) and m1.alternative_index=1) and (s.concept_structure_id = m2.concept_structure_id(+) and m2.alternative_index=2) and (s.concept_structure_id = m3.concept_structure_id(+) and m3.alternative_index=3) and (s.concept_structure_id = m4.concept_structure_id(+) and m4.alternative_index=4) and (s.concept_structure_id = m5.concept_structure_id(+) and m5.alternative_index=5) and (s.concept_structure_id = m6.concept_structure_id(+) and m6.alternative_index=6) and (s.concept_structure_id = m7.concept_structure_id(+) and m7.alternative_index=7) and (s.concept_structure_id = m8.concept_structure_id(+) and m8.alternative_index=8) and (s.concept_structure_id = m9.concept_structure_id(+) and m9.alternative_index=9) and (s.concept_structure_id = m10.concept_structure_id(+) and m10.alternative_index=10) and (s.concept_structure_id = m11.concept_structure_id(+) and m11.alternative_index=11) and (s.concept_structure_id = m12.concept_structure_id(+) and m12.alternative_index=12) and (s.concept_structure_id = m13.concept_structure_id(+) and m13.alternative_index=13) and (s.concept_structure_id = m14.concept_structure_id(+) and m14.alternative_index=14) and (s.concept_structure_id = m15.concept_structure_id(+) and m15.alternative_index=15) UNION ALL SELECT m3.METRICS_ID AS METRICS_OVERVIEW_ID, s.concept_structure_id AS CONCEPT_STRUCTURE_ID ,'SCORE' AS METRIC_TYPE , 85 ,m0.SCORE as METRIC_VALUE0 ,m1.SCORE as METRIC_VALUE1 ,m2.SCORE as METRIC_VALUE2 ,m3.SCORE as METRIC_VALUE3 ,m4.SCORE as METRIC_VALUE4 ,m5.SCORE as METRIC_VALUE5 ,m6.SCORE as METRIC_VALUE6 ,m7.SCORE as METRIC_VALUE7 ,m8.SCORE as METRIC_VALUE8 ,m9.SCORE as METRIC_VALUE9 ,m10.SCORE as METRIC_VALUE10 ,m11.SCORE as METRIC_VALUE11 ,m12.SCORE as METRIC_VALUE12 ,m13.SCORE as METRIC_VALUE13 ,m14.SCORE as METRIC_VALUE14 ,m15.SCORE as METRIC_VALUE15 ,'n' as METRIC_INCOMPLETE0 ,'n' as METRIC_INCOMPLETE1 ,'n' as METRIC_INCOMPLETE2 ,'n' as METRIC_INCOMPLETE3 ,'n' as METRIC_INCOMPLETE4 ,'n' as METRIC_INCOMPLETE5 ,'n' as METRIC_INCOMPLETE6 ,'n' as METRIC_INCOMPLETE7 ,'n' as METRIC_INCOMPLETE8 ,'n' as METRIC_INCOMPLETE9 ,'n' as METRIC_INCOMPLETE10 ,'n' as METRIC_INCOMPLETE11 ,'n' as METRIC_INCOMPLETE12 ,'n' as METRIC_INCOMPLETE13 ,'n' as METRIC_INCOMPLETE14 ,'n' as METRIC_INCOMPLETE15 ,90 - m0.SCORE as METRIC_VARIANCE0 ,90 - m1.SCORE as METRIC_VARIANCE1 ,90 - m2.SCORE as METRIC_VARIANCE2 ,90 - m3.SCORE as METRIC_VARIANCE3 ,90 - m4.SCORE as METRIC_VARIANCE4 ,90 - m5.SCORE as METRIC_VARIANCE5 ,90 - m6.SCORE as METRIC_VARIANCE6 ,90 - m7.SCORE as METRIC_VARIANCE7 ,90 - m8.SCORE as METRIC_VARIANCE8 ,90 - m9.SCORE as METRIC_VARIANCE9 ,90 - m10.SCORE as METRIC_VARIANCE10 ,90 - m11.SCORE as METRIC_VARIANCE11 ,90 - m12.SCORE as METRIC_VARIANCE12 ,90 - m13.SCORE as METRIC_VARIANCE13 ,90 - m14.SCORE as METRIC_VARIANCE14 ,90 - m15.SCORE as METRIC_VARIANCE15 ,(85 - NVL(m0.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m1.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m2.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m3.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m4.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m5.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m6.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m7.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m8.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m9.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m10.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m11.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m12.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m13.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m14.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,(85 - NVL(m15.score, 0)) * 100 / 85 as METRIC_VALUE_NORMALIZED0 ,0 as METRIC_TARGET_NORMALIZED FROM acd_concept_Structure s ,acd_metrics m0 ,acd_metrics m1 ,acd_metrics m2 ,acd_metrics m3 ,acd_metrics m4 ,acd_metrics m5 ,acd_metrics m6 ,acd_metrics m7 ,acd_metrics m8 ,acd_metrics m9 ,acd_metrics m10 ,acd_metrics m11 ,acd_metrics m12 ,acd_metrics m13 ,acd_metrics m14 ,acd_metrics m15 WHERE (s.concept_structure_id = m0.concept_structure_id(+) and m0.alternative_index=0) and (s.concept_structure_id = m1.concept_structure_id(+) and m1.alternative_index=1) and (s.concept_structure_id = m2.concept_structure_id(+) and m2.alternative_index=2) and (s.concept_structure_id = m3.concept_structure_id(+) and m3.alternative_index=3) and (s.concept_structure_id = m4.concept_structure_id(+) and m4.alternative_index=4) and (s.concept_structure_id = m5.concept_structure_id(+) and m5.alternative_index=5) and (s.concept_structure_id = m6.concept_structure_id(+) and m6.alternative_index=6) and (s.concept_structure_id = m7.concept_structure_id(+) and m7.alternative_index=7) and (s.concept_structure_id = m8.concept_structure_id(+) and m8.alternative_index=8) and (s.concept_structure_id = m9.concept_structure_id(+) and m9.alternative_index=9) and (s.concept_structure_id = m10.concept_structure_id(+) and m10.alternative_index=10) and (s.concept_structure_id = m11.concept_structure_id(+) and m11.alternative_index=11) and (s.concept_structure_id = m12.concept_structure_id(+) and m12.alternative_index=12) and (s.concept_structure_id = m13.concept_structure_id(+) and m13.alternative_index=13) and (s.concept_structure_id = m14.concept_structure_id(+) and m14.alternative_index=14) and (s.concept_structure_id = m15.concept_structure_id(+) and m15.alternative_index=15) |