SELECT A.ARREST_ID, A.ARREST_NUM AS ARREST_NUMBER, A.AGENCY_CODE, P.PERSON_ID, P.MASTER_PERSON_ID, A.ARREST_DATE, A.DISCHARGE_DATE, A.ARR_COMMENT AS COMMENTS, (CASE WHEN OFA.FNAME is not null THEN OFA.FNAME ELSE '' END) as ARREST_OFFICER_FNAME, (CASE WHEN OFA.LNAME is not null THEN OFA.LNAME ELSE '' END) as ARREST_OFFICER_LNAME, (CASE WHEN OFT.FNAME is not null THEN OFT.FNAME ELSE '' END) as TRANSPORT_OFFICER_FNAME, (CASE WHEN OFT.LNAME is not null THEN OFT.LNAME ELSE '' END) as TRANSPORT_OFFICER_LNAME, (CASE WHEN OFA.INTERNAL_ID IS NOT NULL THEN OFA.INTERNAL_ID ELSE '' END) AS ARREST_OFFICER_BADGE_ID, '' AS ARREST_PLACE_COUNTY, (CASE WHEN ADR.STREET_NAME IS NOT NULL THEN ADR.STREET_NAME ELSE '' END) AS ARREST_PLACE_STREET_NAME, (CASE WHEN ADR.STREET_NUMBER IS NOT NULL THEN ADR.STREET_NUMBER ELSE '' END) AS ARREST_PLACE_STREET_NUMBER, (CASE WHEN ADR.STREET_CD_STREET_TYPE_CODE IS NOT NULL THEN ADR.STREET_CD_STREET_TYPE_CODE ELSE '' END) AS ARREST_PLACE_STREET_TYPE, (CASE WHEN ADR.CITY IS NOT NULL THEN ADR.CITY ELSE '' END) AS ARREST_PLACE_CITY, (CASE WHEN ADR.STATE_CD_STATE_CODE IS NOT NULL THEN ADR.STATE_CD_STATE_CODE ELSE '' END) AS ARREST_PLACE_STATE, (CASE WHEN ADR.ZIP5 IS NOT NULL THEN ADR.ZIP5 ELSE NULL END) AS ARREST_PLACE_ZIP5, A.CASE_NUMBER FROM ARRESTS A INNER JOIN PEOPLE P ON EJS_GET_PRIMARY_PERSON_ID (A.PERSON_ID) = P.PERSON_ID INNER JOIN MASTER_NAMES MN ON P.PERSON_ID = MN.PER_PERSON_ID LEFT OUTER JOIN VEHICLES V ON A.VEH_VEHICLE_ID = V.VEHICLE_ID LEFT OUTER JOIN EJS_CODES SC ON A.STATUS_CODE = SC.CODE AND A.STATUS_CODE_TYPE = SC.CODE_TYPE LEFT OUTER JOIN ARREST_OFFICERS AOT ON AOT.ARREST_ID=A.ARREST_ID AND AOT.ROLE_CODE='T' LEFT OUTER JOIN OFFICERS OFT ON OFT.OFFICER_ID=AOT.OFFICER_ID LEFT OUTER JOIN ARREST_OFFICERS AOA ON AOA.ARREST_ID=A.ARREST_ID AND AOA.ROLE_CODE='X' LEFT OUTER JOIN OFFICERS OFA ON OFA.OFFICER_ID=AOA.OFFICER_ID LEFT OUTER JOIN ADDRESSES ADR ON ADR.ADDRESS_ID=A.ADDR_ADDRESS_ID WHERE MN.NAME_TYPE_CODE = 'P'