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, GN.NAME AS GANG_NAME, (SELECT ECOD.DESCRIPTION FROM EJS_CODES ECOD WHERE ECOD.CODE_TYPE = GS.GANG_TYPE_CD_TYPE AND ECOD.CODE = GS.GANG_TYPE_CODE) AS GANG_TYPE, GS.GANG_TYPE_CODE, GLC.GANG_LEVEL_DESC AS GANG_LEVEL, GLC.LEVEL_CODE AS GANG_LEVEL_CODE, FG.GANG_ID AS GANG_ID 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_GANGS FG ON FG.FLDINT_ID = FI.FLDINT_ID INNER JOIN GANGS GS ON GS.GANG_ID = FG.GANG_ID INNER JOIN GANG_NAMES GN ON GN.GANG_ID = FG.GANG_ID AND GN.NAME_TYPE_CODE = 'P' INNER JOIN GANG_LEVEL_CODES GLC ON GLC.LEVEL_CODE = GS.LEVEL_CODE LEFT OUTER JOIN CASE_FIELD_INTS CFI ON CFI.FLDINT_ID = FI.FLDINT_ID