View Definition:
SELECT CI.CITATION_ID,
CI.TICKET_NUMBER,
CI.CITATION_TYPE_CODE,
CTC.DESCRIPTION AS CITATION_TYPE_DESC,
CI.FILED_DATE,
CI.CITATION_DATE,
CI.USER_AGENCY,
CI.AGENCY_CODE,
CAC.AGENCY_DESC,
CI.PLAINTIFF,
CI.TWP_RD,
CI.STATE_DIST_CODE,
SDC.DESCRIPTION AS STATE_DIST_DESC,
CI.CITY_CODE,
CC.DESCRIPTION AS CITY_DESC,
CI.COUNTY_CODE,
COC.DESCRIPTION AS COUNTY_DESC,
CI.BOND_TYPE,
BTC.DESCRIPTION AS BOND_TYPE_DESC,
CI.MUST_APPEAR,
CI.BOND_AMOUNT,
CI.POSTED_SPEED,
CI.ACTUAL_SPEED,
CI.RACE_KNOWN,
CI.STOP_COMMENT,
CI.PERCEIVED_RACE_CODE,
PRC.DESCRIPTION AS PERCEIVED_RACE_DESC,
CI.DURATION_CODE,
DC.DESCRIPTION AS DURATION_DESC,
ADDR.ADDRESS_ID,
ADDR.STREET_NUMBER,
ADDR.DIRCT_CD_DIRECTION_CODE,
ADDR.STREET_NAME,
ADDR.STREET_CD_STREET_TYPE_CODE,
ADDR.DIRECT_SUFFIX,
ADDR.ADDR_SC_ADDRESS_SUBTYPE_CODE,
ADDR.SUB_NUMBER,
ADDR.CITY,
ADDR.STATE_CD_STATE_CODE,
ADDR.ZIP5,
ADDR.ZIP4,
ADDR.BEAT || '-' || ADDR.SUB_BEAT,
ADDR.CNTY_CD_COUNTY_CODE,
ADDR.INTRSECT1_NAME,
ADDR.INTRSECT1_DIR,
ADDR.INTRSECT1_STREET_TYPE_CODE,
ADDR.INTRSECT1_DIR_SUFFIX,
EJS_FORMAT_ADDRESS(ADDR.ADDRESS_ID)
FROM CITATIONS CI
INNER JOIN CITATION_TYPE_CODES CTC ON CI.CITATION_TYPE_CODE = CTC.CITATION_TYPE_CODE
INNER JOIN CITATION_ADDRESSES CIADDR ON CI.CITATION_ID = CIADDR.CITATION_ID
INNER JOIN ADDRESSES ADDR ON CIADDR.ADDRESS_ID = ADDR.ADDRESS_ID
INNER JOIN AGENCY_CODES CAC ON CI.AGENCY_CODE = CAC.AGENCY_CODE
INNER JOIN EJS_CODES COC ON CI.COUNTY_CODE = COC.CODE AND CI.COUNTY_CODE_TYPE = COC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES CC ON CI.CITY_CODE = CC.CODE AND CI.CITY_CODE_TYPE = CC.CODE_TYPE
LEFT OUTER JOIN STATE_DISTRICT_CODES SDC ON CI.STATE_DIST_CODE = SDC.STATE_DIST_CODE
LEFT OUTER JOIN BOND_TYPE_CODES BTC ON CI.BOND_TYPE = BTC.BOND_TYPE
LEFT OUTER JOIN EJS_CODES PRC ON CI.PERCEIVED_RACE_CODE = PRC.CODE AND CI.PERCEIVED_RACE_CODE_TYPE = PRC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES DC ON CI.DURATION_CODE = PRC.CODE AND CI.DURATION_CODE_TYPE = PRC.CODE_TYPE
