SELECT DISTINCT datasetid AS datasetid , ds_icr_headerid AS ds_icr_headerid , ds_icr_unique_key AS ds_icr_unique_key , ds_icr_rec_num AS ds_icr_rec_num , NULL AS ds_icr_generic_key1 , ibr_segment AS ibr_segment , MAX(DECODE(data_element_prompt,'Segment Action Type',TRIM(DATA_VALUE))) AS action , MAX(DECODE(data_element_num, '1',TRIM(data_value))) AS de_1 , MAX(incident_nbr) AS de_2 , MAX(DECODE(data_element_num,'14',TRIM(data_value))) AS de_14 , MAX(DECODE(data_element_num,'15',TRIM(data_value))) AS de_15 , MAX(DECODE(data_element_num,'16',TRIM(data_value))) AS de_16 , MAX(DECODE(data_element_num,'17',TRIM(data_value))) AS de_17 , MAX(DECODE(data_element_num,'18',TRIM(data_value))) AS de_18 , MAX(DECODE(data_element_num,'19',TRIM(data_value))) AS de_19 , STRAGG(DECODE(data_element_num,'20',TRIM(data_value))) AS de_20 , MAX(DECODE(data_element_num,'21',TRIM(data_value))) AS de_21 , MAX(DECODE(data_element_num,'22',TRIM(data_value))) AS de_22 FROM ( WITH irpi AS ( SELECT headerid AS ds_icr_headerid , pclass , plosstype , LPAD(TRUNC(SUM(pvalue)),9,'0') AS pvalue FROM ds_icr_property_items GROUP BY headerid , pclass , plosstype HAVING SUM(pvalue) > 0 ) SELECT extsys_code , datasetid , ibr_segment , ibr_field_id , ds_icr_headerid , ds_icr_unique_key , ds_icr_rec_num , NULL AS ds_icr_generic_key1 , data_element_prompt , orig_ibr_value , translated_value , modified_value , modified , data_element_num , nav_order , data_value , incident_nbr , posted FROM ds_ibr_field_values_vw vw WHERE extsys_code = 'NIBRS' AND data_element_num NOT IN ('14', '15', '16', '17') UNION ALL SELECT DISTINCT extsys_code , datasetid , ibr_segment , NULL , ds_icr_headerid , ds_icr_unique_key AS ds_icr_unique_key , irpi.plosstype AS ds_icr_rec_num , irpi.pclass AS ds_icr_generic_key1 , data_element_prompt , orig_ibr_value , translated_value , modified_value , modified , data_element_num , nav_order , data_value , incident_nbr , posted FROM ds_ibr_field_values_vw difvv LEFT JOIN irpi USING (ds_icr_headerid) WHERE data_element_num IN ('1', '2', '14', '15', '16', '17') AND extsys_code = 'NIBRS' AND ibr_segment = 'PROPERTY_ITEMS' AND irpi.pclass IS NOT NULL AND pvalue = difvv.data_value AND ds_icr_unique_key = plosstype UNION ALL SELECT DISTINCT extsys_code , datasetid , ibr_segment , NULL , ds_icr_headerid , ds_icr_unique_key AS ds_icr_unique_key , irpi.plosstype AS ds_icr_rec_num , irpi.pclass AS ds_icr_generic_key1 , data_element_prompt , orig_ibr_value , translated_value , modified_value , modified , data_element_num , nav_order , data_value , incident_nbr , posted FROM ds_ibr_field_values_vw difvv LEFT JOIN irpi USING (ds_icr_headerid) WHERE data_element_num IN ('1', '2', '14') AND extsys_code = 'NIBRS' AND ibr_segment = 'PROPERTY_ITEMS' AND irpi.plosstype = ds_icr_unique_key AND irpi.pclass IS NOT NULL UNION ALL SELECT DISTINCT extsys_code , datasetid , ibr_segment , NULL , ds_icr_headerid , ds_icr_unique_key AS ds_icr_unique_key , irpi.plosstype AS ds_icr_rec_num , irpi.pclass AS ds_icr_generic_key1 , data_element_prompt , orig_ibr_value , translated_value , modified_value , modified , data_element_num , nav_order , irpi.pclass AS data_value , incident_nbr , posted FROM ds_ibr_field_values_vw difvv LEFT JOIN irpi USING (ds_icr_headerid) WHERE data_element_num IN ('15') AND extsys_code = 'NIBRS' AND ibr_segment = 'PROPERTY_ITEMS' AND irpi.plosstype = ds_icr_unique_key AND irpi.pclass IS NOT NULL UNION ALL SELECT DISTINCT extsys_code , datasetid , ibr_segment , NULL , ds_icr_headerid , ds_icr_unique_key AS ds_icr_unique_key , irpi.plosstype AS ds_icr_rec_num , irpi.pclass AS ds_icr_generic_key1 , data_element_prompt , orig_ibr_value , translated_value , modified_value , modified , data_element_num , nav_order , data_value , incident_nbr , posted FROM ds_ibr_field_values_vw difvv LEFT JOIN irpi USING (DS_ICR_HEADERID) WHERE data_element_num IN ('17') AND extsys_code = 'NIBRS' AND ibr_segment = 'PROPERTY_ITEMS' AND irpi.plosstype = ds_icr_unique_key AND irpi.pclass IS NOT NULL ) WHERE ibr_segment='PROPERTY_ITEMS' --AND posted = 'Y' GROUP BY datasetid , ds_icr_headerid , ds_icr_unique_key , ds_icr_rec_num , ds_icr_generic_key1 , ibr_segment ORDER BY ds_icr_headerid