SELECT DISTINCT DATASETID ,DS_ICR_HEADERID ,DS_ICR_UNIQUE_KEY ,DS_ICR_REC_NUM ,NULL AS DS_ICR_GENERIC_KEY1 -- Only Used for PROPERTY ,IBR_SEGMENT ,MAX ( DECODE (DATA_ELEMENT_PROMPT, 'Segment Action Type', TRIM (DATA_VALUE))) AS ACTION ,MAX (DECODE (DATA_ELEMENT_NUM, '1', TRIM (data_value))) DE_1 ,MAX (INCIDENT_NBR) DE_2 ,MAX(DECODE(DATA_ELEMENT_NUM, '14', TRIM(data_value))) DE_14 ,MAX(DECODE(DATA_ELEMENT_NUM, '15', TRIM(data_value))) DE_15 ,MAX(DECODE(DATA_ELEMENT_NUM, '16', TRIM(data_value))) DE_16 ,MAX(DECODE(DATA_ELEMENT_NUM, '17', TRIM(data_value))) DE_17 ,MAX(DECODE(DATA_ELEMENT_NUM, '18', TRIM(data_value))) DE_18 ,MAX(DECODE(DATA_ELEMENT_NUM, '19', TRIM(data_value))) DE_19 ,STRAGG(DECODE(DATA_ELEMENT_NUM, '20', TRIM(data_value))) DE_20 ,MAX(DECODE(DATA_ELEMENT_NUM, '21', TRIM(data_value))) DE_21 ,MAX(DECODE(DATA_ELEMENT_NUM, '22', TRIM(data_value))) DE_22 ,POSTED ,TIMEMADE ,TIMEMARKED ,TIMERECVED ,TIMESENT ,OPEN_STATUS ,RECSFINLED ,RECSSENT ,USERMADE ,USERMARKED ,USERRECVED ,USERSENT 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 ) ,datasetDates AS (SELECT DIDS.DATASETID AS DATASETID, DIDS.TIMEMADE AS TIMEMADE, DIDS.TIMEMARKED AS TIMEMARKED, DIDS.TIMERECVED AS TIMERECVED, DIDS.TIMESENT AS TIMESENT, DIDS.OPEN_STATUS AS OPEN_STATUS, DIDS.RECSFINLED AS RECSFINLED, DIDS.RECSSENT AS RECSSENT, DIDS.USERMADE AS USERMADE, DIDS.USERMARKED AS USERMARKED, DIDS.USERRECVED AS USERRECVED, DIDS.USERSENT AS USERSENT FROM DS_ICR_DATA_SETS DIDS ) 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 ,TIMEMADE ,TIMEMARKED ,TIMERECVED ,TIMESENT ,OPEN_STATUS ,RECSFINLED ,RECSSENT ,USERMADE ,USERMARKED ,USERRECVED ,USERSENT FROM DS_IBR_FIELD_VALUES_VW vw JOIN datasetDates USING (DATASETID) 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 ,TIMEMADE ,TIMEMARKED ,TIMERECVED ,TIMESENT ,OPEN_STATUS ,RECSFINLED ,RECSSENT ,USERMADE ,USERMARKED ,USERRECVED ,USERSENT FROM DS_IBR_FIELD_VALUES_VW difvv LEFT JOIN irpi USING (DS_ICR_HEADERID) JOIN datasetDates USING (DATASETID) 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 ,TIMEMADE ,TIMEMARKED ,TIMERECVED ,TIMESENT ,OPEN_STATUS ,RECSFINLED ,RECSSENT ,USERMADE ,USERMARKED ,USERRECVED ,USERSENT FROM DS_IBR_FIELD_VALUES_VW difvv LEFT JOIN irpi USING (DS_ICR_HEADERID) JOIN datasetDates USING (DATASETID) 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 ,TIMEMADE ,TIMEMARKED ,TIMERECVED ,TIMESENT ,OPEN_STATUS ,RECSFINLED ,RECSSENT ,USERMADE ,USERMARKED ,USERRECVED ,USERSENT FROM DS_IBR_FIELD_VALUES_VW difvv LEFT JOIN irpi USING (DS_ICR_HEADERID) JOIN datasetDates USING (DATASETID) 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 ,TIMEMADE ,TIMEMARKED ,TIMERECVED ,TIMESENT ,OPEN_STATUS ,RECSFINLED ,RECSSENT ,USERMADE ,USERMARKED ,USERRECVED ,USERSENT FROM DS_IBR_FIELD_VALUES_VW difvv LEFT JOIN irpi USING (DS_ICR_HEADERID) JOIN datasetDates USING (DATASETID) 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' GROUP BY DATASETID ,DS_ICR_HEADERID ,DS_ICR_UNIQUE_KEY ,DS_ICR_REC_NUM ,DS_ICR_GENERIC_KEY1 ,IBR_SEGMENT ,POSTED ,TIMEMADE ,TIMEMARKED ,TIMERECVED ,TIMESENT ,OPEN_STATUS ,RECSFINLED ,RECSSENT ,USERMADE ,USERMARKED ,USERRECVED ,USERSENT