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 , ds_icr_generic_key1 AS ds_icr_generic_key1 , MAX(DECODE(data_element_num, '1', TRIM(data_value))) AS de_1 , MAX(DECODE(data_element_num, '2', TRIM(data_value))) AS de_2_short , MAX(incident_nbr) AS de_2 , MAX(DECODE(data_element_num, '2A', TRIM(data_value))) AS de_2A , REPLACE(STRAGG(DECODE(data_element_num, '3', TRIM(data_value))),', ','') AS de_3 -- Replace max on 3 with LISTAGG once we move to 11g check nav_order .2 is included (fix cargo theft report if included) , MAX(DECODE(data_element_num, '4', TRIM(data_value))) AS de_4 , MAX(DECODE(data_element_num, '5', TRIM(data_value))) AS de_5 , MAX(DECODE(data_element_num, '6', TRIM(data_value))) AS de_6 , MAX(DECODE(data_element_num, '7', TRIM(data_value))) AS de_7 , MAX(DECODE(data_element_num, '8', TRIM(data_value))) AS de_8 , MAX(DECODE(data_element_num, '8A', TRIM(data_value))) AS de_8A , MAX(DECODE(data_element_num, '9', TRIM(data_value))) AS de_9 , MAX(DECODE(data_element_num, '10', TRIM(data_value))) AS de_10 , MAX(DECODE(data_element_num, '11', TRIM(data_value))) AS de_11 , STRAGG(DECODE(data_element_num, '12', TRIM(data_value))) AS de_12 , MAX(DECODE(data_element_num, '13', TRIM(data_value))) AS de_13 , 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 , MAX(DECODE(data_element_num, '23', TRIM(data_value))) AS de_23 --, MAX(DECODE(data_element_num, '24', TRIM(data_value))) AS de_24 , MIN(DECODE(data_element_num, '24', TRIM(data_value))) AS de_24 , MAX(DECODE(data_element_num, '25', TRIM(data_value))) AS de_25 , MAX(DECODE(data_element_num, '25A', TRIM(data_value))) AS de_25A , MAX(DECODE(data_element_num, '25B', TRIM(data_value))) AS de_25B , MAX(DECODE(data_element_num, '25C', TRIM(data_value))) AS de_25C , MAX(DECODE(data_element_num, '26', TRIM(data_value))) AS de_26 , MAX(DECODE(data_element_num, '27', TRIM(data_value))) AS de_27 , MAX(DECODE(data_element_num, '28', TRIM(data_value))) AS de_28 , MAX(DECODE(data_element_num, '29', TRIM(data_value))) AS de_29 , MAX(DECODE(data_element_num, '30', TRIM(data_value))) AS de_30 , MAX(DECODE(data_element_num, '31', TRIM(data_value))) AS de_31 , MAX(DECODE(data_element_num, '32', TRIM(data_value))) AS de_32 , MAX(DECODE(data_element_num, '33', TRIM(data_value))) AS de_33 --, MAX(DECODE(data_element_num, '34', TRIM(data_value))) AS de_34 , MIN(DECODE(data_element_num, '34', TRIM(data_value))) AS de_34 , MAX(DECODE(data_element_num, '35', TRIM(data_value))) AS de_35 , MAX(DECODE(data_element_num, '36', TRIM(data_value))) AS de_36 , MAX(DECODE(data_element_num, '37', TRIM(data_value))) AS de_37 , MAX(DECODE(data_element_num, '38', TRIM(data_value))) AS de_38 , MAX(DECODE(data_element_num, '39', TRIM(data_value))) AS de_39 , MAX(DECODE(data_element_num, '39A', TRIM(data_value))) AS de_39A , MAX(DECODE(data_element_num, '40', TRIM(data_value))) AS de_40 , MAX(DECODE(data_element_num, '41', TRIM(data_value))) AS de_41 , MAX(DECODE(data_element_num, '42', TRIM(data_value))) AS de_42 , MAX(DECODE(data_element_num, '43', TRIM(data_value))) AS de_43 , MAX(DECODE(data_element_num, '44', TRIM(data_value))) AS de_44 , MAX(DECODE(data_element_num, '45', TRIM(data_value))) AS de_45 , MAX(DECODE(data_element_num, '46', TRIM(data_value))) AS de_46 , MAX(DECODE(data_element_num, '47', TRIM(data_value))) AS de_47 , MAX(DECODE(data_element_num, '48', TRIM(data_value))) AS de_48 , MAX(DECODE(data_element_num, '49', TRIM(data_value))) AS de_49 , MAX(DECODE(data_element_num, '50', TRIM(data_value))) AS de_50 , MAX(DECODE(data_element_num, '51', TRIM(data_value))) AS de_51 , MAX(DECODE(data_element_num, '52', TRIM(data_value))) AS de_52 FROM ( WITH irpi AS ( SELECT headerid AS ds_icr_headerid , pclass AS pclass , plosstype AS 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 AS extsys_code , datasetid AS datasetid , ibr_segment AS ibr_segment , ibr_field_id AS ibr_field_id , 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 , data_element_prompt AS data_element_prompt , orig_ibr_value AS orig_ibr_value , translated_value AS translated_value , modified_value AS modified_value , modified AS modified , data_element_num AS data_element_num , nav_order AS nav_order , data_value AS data_value , incident_nbr AS incident_nbr , posted AS 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 AS extsys_code , datasetid AS datasetid , ibr_segment AS ibr_segment , NULL AS ibr_field_id -- **** Why is this NULL , ds_icr_headerid AS 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 AS data_element_prompt , orig_ibr_value AS orig_ibr_value , translated_value AS translated_value , modified_value AS modified_value , modified AS modified , data_element_num AS data_element_num , nav_order AS nav_order , data_value AS data_value , incident_nbr AS incident_nbr , posted AS 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 AS extsys_code , datasetid AS datasetid , ibr_segment AS ibr_segment , NULL AS ibr_field_id -- **** Why is this NULL , ds_icr_headerid AS 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 AS data_element_prompt , orig_ibr_value AS orig_ibr_value , translated_value AS translated_value , modified_value AS modified_value , modified AS modified , data_element_num AS data_element_num , nav_order AS nav_order , data_value AS data_value , incident_nbr AS incident_nbr , posted AS 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 AS extsys_code , datasetid AS datasetid , ibr_segment AS ibr_segment , NULL AS ibr_field_id -- **** Why is this NULL , ds_icr_headerid AS 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 AS data_element_prompt , orig_ibr_value AS orig_ibr_value , translated_value AS translated_value , modified_value AS modified_value , modified AS modified , data_element_num AS data_element_num , nav_order AS nav_order , irpi.pclass AS data_value -- **** Why is this pclass , incident_nbr AS incident_nbr , posted AS 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 AS extsys_code , datasetid AS datasetid , ibr_segment AS ibr_segment , NULL AS ibr_field_id -- **** Why is this NULL , ds_icr_headerid AS 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 AS data_element_prompt , orig_ibr_value AS orig_ibr_value , translated_value AS translated_value , modified_value AS modified_value , modified AS modified , data_element_num AS data_element_num , nav_order AS nav_order , data_value AS data_value , incident_nbr AS incident_nbr , posted AS 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 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