SELECT X."DISPATCH_NUMBER",X."DISPATCH_ID",X."UPDATOR_DATE",X."ORI",X."AGENCY_CODE",X."AGENCY",X."REPORT_FLAG",X."EVENT_RELATIONSHIP",X."REPORT_NUMBER",X."CALLER_ADDRESS",X."ADDRESS",X."PLACE_NAME",X."LOCATION_TYPE",X."BEAT",X."RD",X."DISTRICT",X."CITY",X."HOW_RECEIVED",X."INITIAL_TYPE",X."DISPOSITION",X."CALL_PRIORITY",X."DATE_OCCURRED",X."DATE_TIME_RECIEVED",X."X_COORDINATE",X."Y_COORDINATE",X."EDIT_DATE",X."ACTION",X."DATETIME_ARCHIVED",X."INCIDENT_CODE",X."CALL_TAKER",X."CONTACTING_OFFICER",X."COMPLAINANT",X."CURRENT_PHONE",X."COMPLAINANT_ADDRESS",X."COMPLAINANT_COORDINATEX",X."COMPLAINANT_COORDINATEY",X."DISP_STATUS_CODE",X."APARTMENTNUMBER", REPLACE(REPLACE(DEOUTER.INCIDENT_NOTE,CHR(10),' '),CHR(13),' ') AS DISPATCHER_COMMENT FROM ( SELECT DISTINCT DE.DISPATCH_NUMBER ,DE.DISPATCH_ID ,DE.UPDATOR_DATE ,CASE WHEN AGC.ORI_NUMBER IS NULL THEN DE.AGENCY ELSE AGC.ORI_NUMBER END AS ORI ,DE.AGENCY AS AGENCY_CODE ,CASE WHEN AGC.ORI_NUMBER IS NULL THEN DE.AGENCY ELSE AGC.ORI_NUMBER END || CASE WHEN AGC.AGENCY_DESC IS NOT NULL THEN ' ' || AGC.AGENCY_DESC ELSE '' END AS AGENCY ,DEC.ASSIGNED_INCIDENT AS REPORT_FLAG ,'' AS EVENT_RELATIONSHIP ,DEC.INC_REPORT_NUMBER AS REPORT_NUMBER ,DE.CALLER_LOCATION AS CALLER_ADDRESS ,CASE WHEN DE.STREET_NUMBER IS NOT NULL AND DE.STREET_NUMBER != ' ' THEN DE.STREET_NUMBER || ' ' ELSE '' END || CASE WHEN DE.STREET_DIRECTION IS NOT NULL THEN DE.STREET_DIRECTION || ' ' ELSE '' END || CASE WHEN DE.STREET_NAME IS NOT NULL AND DE.STREET_NAME != ' ' THEN DE.STREET_NAME || ' ' ELSE '' END || CASE WHEN DE.STREET_CD_STREET_TYPE_CODE IS NOT NULL THEN DE.STREET_CD_STREET_TYPE_CODE || ' ' ELSE '' END || CASE WHEN DE.STREET_DIR_SUFFIX IS NOT NULL AND DE.STREET_DIR_SUFFIX != ' ' THEN DE.STREET_DIR_SUFFIX || ' ' ELSE '' END || CASE WHEN DE.CITY IS NOT NULL AND DE.CITY != ' ' THEN DE.CITY || ' ' ELSE '' END || CASE WHEN DE.STATE IS NOT NULL AND DE.STATE != ' ' THEN DE.STATE || ' ' ELSE '' END || CASE WHEN DE.ZIP5 IS NOT NULL AND DE.ZIP5 != '' THEN DE.ZIP5 || '' ELSE '' END AS ADDRESS ,DE.COMMON_PLACE_NAME AS PLACE_NAME ,LT.DESCRIPTION AS LOCATION_TYPE ,DE.BEAT AS BEAT ,DE.SUB_BEAT AS RD ,CASE WHEN DE.SUB_BEAT IS NOT NULL THEN DE.BEAT || '-' || DE.SUB_BEAT ELSE DE.BEAT END AS DISTRICT ,DE.CITY ,CRC.DESCRIPTION AS HOW_RECEIVED ,CTC.CALL_TYPE_DESC AS INITIAL_TYPE ,CDC.CALL_DISPOSITION_DESC AS DISPOSITION ,CPC.DESCRIPTION AS CALL_PRIORITY ,CASE WHEN I.START_DATE IS NULL THEN TO_CHAR (DE.CALL_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (DE.CALL_DATE, 'HH24:MI:SS') || 'Z' ELSE TO_CHAR (I.START_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (I.START_DATE, 'HH24:MI:SS') || 'Z' END AS DATE_OCCURRED ,TO_CHAR (DE.CALL_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (DE.CALL_DATE, 'HH24:MI:SS') || 'Z' AS DATE_TIME_RECIEVED ,DE.LATITUDE AS X_COORDINATE ,DE.LONGITUDE AS Y_COORDINATE ,TO_CHAR (DE.UPDATOR_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (DE.UPDATOR_DATE, 'HH24:MI:SS') || 'Z' AS EDIT_DATE ,'ADD' AS ACTION ,CASE WHEN DE.CLOSE_DATE IS NOT NULL THEN TO_CHAR (DE.CLOSE_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (DE.CLOSE_DATE, 'HH24:MI:SS') || 'Z' ELSE '' END AS DATETIME_ARCHIVED ,CASE WHEN OC.OFFENSE_CODE IS NOT NULL THEN OC.OFFENSE_CODE || '-' || OC.OFFENSE_DESC ELSE '' END AS INCIDENT_CODE ,DE.DISPATCH_USER AS CALL_TAKER ,DEO.OFFICER_LNAME || ' ' || DEO.OFFICER_FNAME AS CONTACTING_OFFICER ,DE.CALLER_NAME AS COMPLAINANT ,DE.CALLER_PHONE AS CURRENT_PHONE ,CASE WHEN AD.STREET_NUMBER IS NOT NULL THEN AD.STREET_NUMBER || ' ' ELSE '' END || CASE WHEN AD.DIRCT_CD_DIRECTION_CODE IS NOT NULL THEN AD.DIRCT_CD_DIRECTION_CODE || ' ' ELSE '' END || CASE WHEN AD.STREET_NAME IS NOT NULL THEN AD.STREET_NAME || ' ' ELSE '' END || CASE WHEN AD.STREET_CD_STREET_TYPE_CODE IS NOT NULL THEN AD.STREET_CD_STREET_TYPE_CODE || ' ' ELSE '' END || CASE WHEN AD.DIRECT_SUFFIX IS NOT NULL THEN AD.DIRECT_SUFFIX || ' ' ELSE '' END || CASE WHEN AD.CITY IS NOT NULL THEN AD.CITY || ' ' ELSE '' END || CASE WHEN AD.STATE_CD_STATE_CODE IS NOT NULL THEN AD.STATE_CD_STATE_CODE || ' ' ELSE '' END || CASE WHEN AD.ZIP5 IS NOT NULL THEN AD.ZIP5 || ' ' ELSE '' END AS COMPLAINANT_ADDRESS ,AD.LATITUDE AS COMPLAINANT_COORDINATEX ,AD.LONGITUDE AS COMPLAINANT_COORDINATEY ,DSC.DESCRIPTION AS DISP_STATUS_CODE ,DE.SUB_TYPE || CASE WHEN DE.SUB_TYPE IS NOT NULL AND DE.SUB_NUMBER IS NOT NULL THEN ',' || DE.SUB_NUMBER ELSE DE.SUB_NUMBER END AS APARTMENTNUMBER FROM DISPATCH_EVENTS DE LEFT JOIN AGENCY_CODES AGC ON DE.AGENCY = AGC.AGENCY_CODE LEFT JOIN ( SELECT DSEC.* FROM DISPATCH_EVENT_CASES DSEC JOIN ( SELECT DISPATCH_ID, MIN (INCIDENT_ID) AS INCIDENT_ID FROM DISPATCH_EVENT_CASES GROUP BY DISPATCH_ID) DSECS ON DSEC.DISPATCH_ID = DSECS.DISPATCH_ID AND DSEC.INCIDENT_ID = DSECS.INCIDENT_ID) DEC ON DE.DISPATCH_ID = DEC.DISPATCH_ID LEFT JOIN INCIDENTS I ON DEC.INCIDENT_ID = I.INCIDENT_ID LEFT JOIN DISPATCH_EVENT_OFFICERS DEO ON DE.DISPATCH_ID = DEO.DISPATCH_ID AND DEO.PRIMARY = 'Y' LEFT JOIN OFFENSES O ON DEC.INCIDENT_ID = O.INC_INCIDENT_ID LEFT JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE LEFT JOIN EJS_CODES LT ON O.PLACE_PLACE_CODE = LT.CODE AND O.PLACE_CODE_TYPE = LT.CODE_TYPE LEFT JOIN INCIDENT_ADDRESSES IA ON I.INCIDENT_ID = IA.INCIDENT_ID LEFT JOIN ADDRESSES AD ON IA.ADDRESS_ID = AD.ADDRESS_ID LEFT JOIN CALL_TYPE_CODES CTC ON DE.CALL_TYPE = CTC.CALL_TYPE_CODE LEFT JOIN CALL_DISPOSITION_CODES CDC ON DE.CALL_DISPOSITION_CODE = CDC.CALL_DISPOSITION_CODE AND DE.EVENT_TYPE_CODE = CDC.EVENT_TYPE_CODE LEFT JOIN CALL_RECEIVED_CODES CRC ON DE.CALL_RECEIVED_CODE = CRC.CALL_RECEIVED_CODE LEFT JOIN EJS_CODES CPC ON DE.CALL_PRIORITY = CPC.CODE AND DE.CALL_PRIORITY_TYPE = CPC.CODE_TYPE LEFT JOIN EJS_CODES DSC ON DE.DISP_STATUS_CODE = DSC.CODE AND DE.DISP_STATUS_CODE_TYPE = DSC.CODE_TYPE) X INNER JOIN DISPATCH_EVENTS DEOUTER ON X.DISPATCH_ID = DEOUTER.DISPATCH_ID