SELECT FI.FLDINT_ID, FI.AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, FI.AGENCY_ONLY_YN AS AGENCY_ONLY, FI.FLDINT_DATE, FI.FLDINT_DATE AS SORT_DATE, FI.FLDINT_TYPE AS FLDINT_TYPE, EC.DESCRIPTION AS FLDINT_TYPE_DESC, FI.INTELL_ONLY_YN AS INTEL_ONLY, FI.NOTE_ID AS NOTE_ID, FI.SECURITY_LEVEL AS SECURITY_LEVEL, (SELECT ISLC.INC_SEC_LEVEL_DESC FROM INCIDENT_SECURITY_LEVEL_CODES ISLC WHERE ISLC.INC_SEC_LEVEL_CODE = FI.SECURITY_LEVEL) AS SECURITY_DESC, FI.SUMMARY AS SUMMARY, CASE WHEN CFI.CASE_ID IS NOT NULL THEN 'Y' ELSE 'N' END AS IS_ASSOCIATED_TO_CASE, ORS.INTERNAL_ID AS INTERNAL_ID, ORS.LNAME AS LNAME, ORS.FNAME AS FNAME, ORS.TITLE AS TITLE, FIO.ROLE_CODE AS OFF_ROLE_CODE, (SELECT ORC.ROLE_DESC FROM OFFICER_ROLE_CODES ORC WHERE ORC.ROLE_CODE = FIO.ROLE_CODE) AS OFF_ROLE, ORS.AGNCY_CD_AGENCY_CODE AS OFF_AGENCY_CODE, (SELECT AGC.AGENCY_DESC FROM AGENCY_CODES AGC WHERE AGC.AGENCY_CODE = ORS.AGNCY_CD_AGENCY_CODE) AS OFF_AGENCY FROM FIELD_INTERVIEWS FI INNER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = FI.AGENCY_CODE INNER JOIN EJS_CODES EC ON EC.CODE_TYPE = FI.FLDINT_TYPE_TYPE AND EC.CODE = FI.FLDINT_TYPE INNER JOIN FLDINT_OFFICERS FIO ON FIO.FLDINT_ID = FI.FLDINT_ID INNER JOIN OFFICERS ORS ON ORS.OFFICER_ID = FIO.OFFICER_ID LEFT OUTER JOIN CASE_FIELD_INTS CFI ON CFI.FLDINT_ID = FI.FLDINT_ID