QP_DENORM_MATRIX_RULES_VL

Details

• Schema: FUSION

• Object owner: QP

• Object type: VIEW

Columns

Name
DYNAMIC_MATRIX_ID DYNAMIC_MATRIX_RULE_ID DYNAMIC_MATRIX_CLASS_ID ATTRIBUTE_1 ATTRIBUTE_VALUE_1 ATTRIBUTE_2 ATTRIBUTE_VALUE_2 ATTRIBUTE_3 ATTRIBUTE_VALUE_3 ATTRIBUTE_4 ATTRIBUTE_VALUE_4 ATTRIBUTE_5 ATTRIBUTE_VALUE_5 ATTRIBUTE_6 ATTRIBUTE_VALUE_6 ATTRIBUTE_7 ATTRIBUTE_VALUE_7 ATTRIBUTE_8 ATTRIBUTE_VALUE_8 ATTRIBUTE_9 ATTRIBUTE_VALUE_9 ATTRIBUTE_10 ATTRIBUTE_VALUE_10

Query

SQL_Statement

SELECT dynamic_matrix_id, dynamic_matrix_rule_id, max(dynamic_matrix_class_id) dynamic_matrix_class_id, max(case when DIMENSION_NO='Condition1' then ATTRIBUTE_NAME when DIMENSION_NO='Result10' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_1, max(case when DIMENSION_NO='Condition1' then COLUMN_VALUE when DIMENSION_NO='Result10' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_1, max(case when DIMENSION_NO='Condition2' then ATTRIBUTE_NAME when DIMENSION_NO='Result9' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_2, max(case when DIMENSION_NO='Condition2' then COLUMN_VALUE when DIMENSION_NO='Result9' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_2, max(case when DIMENSION_NO='Condition3' then ATTRIBUTE_NAME when DIMENSION_NO='Result8' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_3, max(case when DIMENSION_NO='Condition3' then COLUMN_VALUE when DIMENSION_NO='Result8' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_3, max(case when DIMENSION_NO='Condition4' then ATTRIBUTE_NAME when DIMENSION_NO='Result7' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_4, max(case when DIMENSION_NO='Condition4' then COLUMN_VALUE when DIMENSION_NO='Result7' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_4, max(case when DIMENSION_NO='Condition5' then ATTRIBUTE_NAME when DIMENSION_NO='Result6' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_5, max(case when DIMENSION_NO='Condition5' then COLUMN_VALUE when DIMENSION_NO='Result6' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_5, max(case when DIMENSION_NO='Condition6' then ATTRIBUTE_NAME when DIMENSION_NO='Result5' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_6, max(case when DIMENSION_NO='Condition6' then COLUMN_VALUE when DIMENSION_NO='Result5' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_6, max(case when DIMENSION_NO='Condition7' then ATTRIBUTE_NAME when DIMENSION_NO='Result4' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_7, max(case when DIMENSION_NO='Condition7' then COLUMN_VALUE when DIMENSION_NO='Result4' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_7, max(case when DIMENSION_NO='Condition8' then ATTRIBUTE_NAME when DIMENSION_NO='Result1' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_8, max(case when DIMENSION_NO='Condition8' then COLUMN_VALUE when DIMENSION_NO='Result1' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_8, max(case when DIMENSION_NO='Condition9' then ATTRIBUTE_NAME when DIMENSION_NO='Result2' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_9, max(case when DIMENSION_NO='Condition9' then COLUMN_VALUE when DIMENSION_NO='Result2' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_9, max(case when DIMENSION_NO='Condition10' then ATTRIBUTE_NAME when DIMENSION_NO='Result3' then ATTRIBUTE_NAME ELSE NULL END) ATTRIBUTE_10, max(case when DIMENSION_NO='Condition10' then COLUMN_VALUE when DIMENSION_NO='Result3' then COLUMN_VALUE ELSE NULL END) ATTRIBUTE_VALUE_10 from (select source_code_name as attribute_name,attribute_type,column_value,b.dynamic_matrix_rule_id,a.dynamic_matrix_id,a.dynamic_matrix_class_id, dimension_type||(row_number() over (partition by a.DYNAMIC_MATRIX_ID,b.dynamic_matrix_rule_id,dimension_type order by DISPLAY_SEQUENCE_NUM)) as DIMENSION_NO from ( select b.source_code_name,'VALUE_'||b.DATA_TYPE_CODE||id1.MAP_TO_RULE_COL_NUM As attribute_type,dimension_type,b.DISPLAY_SEQUENCE_NUM as DISPLAY_SEQUENCE_NUM,id1.dynamic_matrix_id,b.dynamic_matrix_class_id from fusion.qp_matrix_dimensions_b b, qp_matrix_inherited_dimensions id1 where b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id ) a, ( select COLUMN_NAME,COLUMN_VALUE,dynamic_matrix_rule_id,dynamic_matrix_id from ( select NVL(TO_CHAR(VALUE_NUMBER1),' ') VALUE_NUMBER1,NVL(TO_CHAR(VALUE_NUMBER2),' ') VALUE_NUMBER2,NVL(TO_CHAR(VALUE_NUMBER3),' ') VALUE_NUMBER3,NVL(TO_CHAR(VALUE_NUMBER4),' ') VALUE_NUMBER4, NVL(TO_CHAR(VALUE_NUMBER5),' ') VALUE_NUMBER5,NVL(TO_CHAR(VALUE_NUMBER6),' ') VALUE_NUMBER6,NVL(TO_CHAR(VALUE_NUMBER7),' ') VALUE_NUMBER7,NVL(TO_CHAR(VALUE_NUMBER8),' ') VALUE_NUMBER8, NVL(TO_CHAR(VALUE_NUMBER9),' ') VALUE_NUMBER9,NVL(TO_CHAR(VALUE_NUMBER10),' ') VALUE_NUMBER10, NVL(VALUE_STRING1,' ') VALUE_STRING1,NVL(VALUE_STRING2,' ') VALUE_STRING2,NVL(VALUE_STRING3,' ') VALUE_STRING3,NVL(VALUE_STRING4,' ') VALUE_STRING4,NVL(VALUE_STRING5,' ') VALUE_STRING5, NVL(VALUE_STRING6,' ') VALUE_STRING6,NVL(VALUE_STRING7,' ') VALUE_STRING7,NVL(VALUE_STRING8,' ') VALUE_STRING8,NVL(VALUE_STRING9,' ') VALUE_STRING9,NVL(VALUE_STRING10,' ') VALUE_STRING10, NVL(TO_CHAR(VALUE_DATE1,'YYYY-MM-DD'),' ') VALUE_DATE1,NVL(TO_CHAR(VALUE_DATE2,'YYYY-MM-DD'),' ') VALUE_DATE2,NVL(TO_CHAR(VALUE_DATE3,'YYYY-MM-DD'),' ') VALUE_DATE3,NVL(TO_CHAR(VALUE_DATE4,'YYYY-MM-DD'),' ') VALUE_DATE4, NVL(TO_CHAR(VALUE_DATE5,'YYYY-MM-DD'),' ') VALUE_DATE5,NVL(TO_CHAR(VALUE_DATE6,'YYYY-MM-DD'),' ') VALUE_DATE6,NVL(TO_CHAR(VALUE_DATE7,'YYYY-MM-DD'),' ') VALUE_DATE7,NVL(TO_CHAR(VALUE_DATE8,'YYYY-MM-DD'),' ') VALUE_DATE8, NVL(TO_CHAR(VALUE_DATE9,'YYYY-MM-DD'),' ') VALUE_DATE9,NVL(TO_CHAR(VALUE_DATE10,'YYYY-MM-DD'),' ') VALUE_DATE10, NVL(TO_CHAR(VALUE_TIMESTAMP1,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP1,NVL(TO_CHAR(VALUE_TIMESTAMP2,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP2,NVL(TO_CHAR(VALUE_TIMESTAMP3,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP3, NVL(TO_CHAR(VALUE_TIMESTAMP4,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP4,NVL(TO_CHAR(VALUE_TIMESTAMP5,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP5,NVL(TO_CHAR(VALUE_TIMESTAMP6,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP6, NVL(TO_CHAR(VALUE_TIMESTAMP7,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP7,NVL(TO_CHAR(VALUE_TIMESTAMP8,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP8,NVL(TO_CHAR(VALUE_TIMESTAMP9,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP9, NVL(TO_CHAR(VALUE_TIMESTAMP10,'YYYY-MM-DD HH:MM:SS'),' ') VALUE_TIMESTAMP10, dynamic_matrix_id,dynamic_matrix_rule_id from qp_matrix_rules) UNPIVOT( COLUMN_VALUE FOR (COLUMN_NAME) IN(VALUE_NUMBER1,VALUE_NUMBER2,VALUE_NUMBER3,VALUE_NUMBER4,VALUE_NUMBER5, VALUE_NUMBER6,VALUE_NUMBER7,VALUE_NUMBER8,VALUE_NUMBER9,VALUE_NUMBER10, VALUE_STRING1,VALUE_STRING2,VALUE_STRING3,VALUE_STRING4,VALUE_STRING5,VALUE_STRING6,VALUE_STRING7,VALUE_STRING8,VALUE_STRING9,VALUE_STRING10,VALUE_DATE1,VALUE_DATE2,VALUE_DATE3,VALUE_DATE4,VALUE_DATE5,VALUE_DATE6,VALUE_DATE7,VALUE_DATE8,VALUE_DATE9,VALUE_DATE10,VALUE_TIMESTAMP1,VALUE_TIMESTAMP2,VALUE_TIMESTAMP3,VALUE_TIMESTAMP4,VALUE_TIMESTAMP5,VALUE_TIMESTAMP6,VALUE_TIMESTAMP7,VALUE_TIMESTAMP8,VALUE_TIMESTAMP9,VALUE_TIMESTAMP10) ) ) b where a.attribute_type = b.COLUMN_NAME and a.dynamic_matrix_id = b.dynamic_matrix_id) group by dynamic_matrix_id,dynamic_matrix_rule_id