WITH w$H AS -- JIRA 55326 Filter out all of the REPLACE records ( SELECT DISTINCT datasetid AS datasetid , ds_icr_headerid AS ds_icr_headerid FROM ds_ibr_field_values_vw WHERE ibr_segment = 'HEADERS' AND posted = 'Y' AND data_element_prompt='Segment Action Type' AND data_value = 'I' MINUS SELECT DISTINCT datasetid AS datasetid , ds_icr_headerid AS ds_icr_headerid FROM ds_ibr_field_values_vw WHERE ibr_segment = 'HEADERS' AND posted = 'Y' AND data_element_prompt='Segment Action Type' AND data_value = 'D' ) SELECT DISTINCT d.datasetid AS datasetid , d.ds_icr_headerid AS ds_icr_headerid , d.ds_icr_unique_key AS ds_icr_unique_key , d.ds_icr_rec_num AS ds_icr_rec_num , NULL AS ds_icr_generic_key1 , d.ibr_segment AS ibr_segment , MAX(DECODE(d.data_element_prompt,'Segment Action Type',TRIM(d.data_value))) AS action , MAX(DECODE(d.data_element_num,'1',TRIM(d.data_value))) AS de_1 , MAX(DECODE(d.data_element_num,'2',TRIM(d.data_value))) AS de_2_short , MAX(d.incident_nbr) AS de_2 , MAX(DECODE(d.data_element_num,'2A',TRIM(d.data_value))) AS de_2A , REPLACE(STRAGG(DECODE(d.data_element_num,'3',TRIM(d.data_value))), ', ', '') AS de_3 , MAX(DECODE(d.data_element_num,'4',TRIM(d.data_value))) AS de_4 , MAX(DECODE(d.data_element_num,'5',TRIM(d.data_value))) AS de_5 , MAX(DECODE(d.nav_order,'12.01',TRIM(d.data_value))) AS excep_off_code -- RMS DOES NOT SUPPORT MORE THAN ONE OFFENSE CLEARED FROM ds_ibr_field_values_vw d JOIN w$H w$H ON w$H.datasetid=d.datasetid AND w$H.ds_icr_headerid=d.ds_icr_headerid WHERE d.ibr_segment='HEADERS' GROUP BY d.datasetid , d.ds_icr_headerid , d.ds_icr_unique_key , d.ds_icr_rec_num , d.ibr_segment ORDER BY d.ds_icr_headerid