View slot9.WORLD.TSTA.JS_INCIDENT_ADDRESS_VW
Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
INCIDENT_ID number 0
INCIDENTS.INCIDENT_ID Implied Constraint R
INC_REPORT_NUMBER varchar2 20
AGENCY_CODE varchar2 30  √  null
AGENCY_CODES.AGENCY_CODE Implied Constraint R
AGENCY_DESC varchar2 40  √  null
REPORT_DATE date 7
START_DATE date 7  √  null
END_DATE date 7  √  null
STATUS varchar2 30
STATUS_CODE varchar2 30
STATUS_DESC varchar2 4000  √  null
SUPP_SEQ number 0
RESPONSIBLE_USER_ID varchar2 100  √  null
AGENCY_ONLY varchar2 1  √  null
CID varchar2 1  √  null
SECURITY_LEVEL number 38  √  null
APPROVING_AGENCY_CODE varchar2 30  √  null
APPROVING_AGENCY_DESC varchar2 40  √  null
SUMMARY varchar2 1000  √  null
ADDRESS_ID number 0  √  null
ADDRESSES.ADDRESS_ID Implied Constraint R
ADDRESS_SUMMARY varchar2 4000  √  null
STREET_NUMBER varchar2 10  √  null
DIRECTION_CODE varchar2 30  √  null
DIRECTION_CODE_TYPE varchar2 30  √  null
STREET_NAME varchar2 40  √  null
STREET_TYPE_CODE varchar2 30  √  null
STREET_TYPE_CODE_TYPE varchar2 30  √  null
STREET_TYPE_DESC varchar2 4000  √  null
ADDRESS_SUBTYPE_CODE varchar2 30  √  null
ADDRESS_SUBTYPE_DESC varchar2 4000  √  null
SUB_NUMBER varchar2 50  √  null
INTRSECT1_STREET_NUMBER varchar2 10  √  null
INTRSECT1_DIR varchar2 30  √  null
INTRSECT1_NAME varchar2 40  √  null
INTRSECT1_STREET_TYPE_CODE varchar2 30  √  null
INTRSECT1_STREET_TYPE_DESC varchar2 4000  √  null
INTRSECT1_DIR_SUFFIX varchar2 30  √  null
LONGITUDE varchar2 30  √  null
LATITUDE varchar2 30  √  null
ADDR_CITY varchar2 40  √  null
GEO_CITY varchar2 40  √  null
STATE_CODE varchar2 30  √  null
STATE_DESC varchar2 4000  √  null
ZIP5 varchar2 30  √  null
ZIP4 varchar2 5  √  null
DIRECT_SUFFIX varchar2 30  √  null
INTERNAL_INCIDENT varchar2 1  √  null
COMMON_PLACE_NAME varchar2 80  √  null
INC_ADDR_COMMENT varchar2 255  √  null
ADDITIONAL_INFO varchar2 255  √  null
REPORTING_AREA varchar2 61  √  null
BEAT varchar2 30  √  null
SUB_BEAT varchar2 30  √  null
LOC_AGENCY_CODE varchar2 30  √  null
LOC_AGENCY_DESC varchar2 40  √  null
CITY varchar2 40  √  null
CITY_DESC varchar2 4000  √  null
COUNTY_CODE varchar2 30  √  null
COUNTY_DESC varchar2 4000  √  null
TOWNSHIP_CODE varchar2 30  √  null
TOWNSHIP_DESC varchar2 4000  √  null

Analyzed at Mon Sep 20 21:05 MDT 2021

View Definition:
SELECT DISTINCT i.incident_id, i.inc_report_number, isupp.supp_agency_code, (select ag.agency_desc from agency_codes ag where isupp.supp_agency_code = ag.agency_code), i.report_date, i.start_date, i.end_date, isupp.isc_status_code, isupp.isc_status_code, (select isc.description from ejs_codes isc where isc.code_type = isupp.isc_status_code_type and isc.code = isupp.isc_status_code), isupp.supp_seq, isupp.responsible_user_id, isupp.agency_only, isupp.cid, isupp.security_level, ISUPP.APPROVING_AGENCY_CODE, (select ag2.agency_desc from agency_codes ag2 where ISUPP.APPROVING_AGENCY_CODE = ag2.agency_code), i.summary, a.address_id, EJS_FORMAT_ADDRESS(A.ADDRESS_ID), a.street_number, a.dirct_cd_direction_code, a.dirct_cd_direction_code_type, a.street_name, a.street_cd_street_type_code, a.street_cd_street_type_cod_type, (select s.description from ejs_codes s where s.code_type = a.street_cd_street_type_cod_type and s.code = a.street_cd_street_type_code), a.addr_sc_address_subtype_code, (select ac.description from ejs_codes ac where ac.code_type = a.addr_sc_address_subtype_c_type and ac.code = a.addr_sc_address_subtype_code), a.sub_number, a.intrsect1_street_number, a.intrsect1_dir, a.intrsect1_name, a.intrsect1_street_type_code, (select int.description from ejs_codes int where int.code_type = a.intrsect1_street_type_cod_type and int.code = a.intrsect1_street_type_code), a.intrsect1_dir_suffix, a.longitude, a.latitude, INITCAP (a.city), INITCAP (a.geo_city), a.state_cd_state_code, (select st.description from ejs_codes st where st.code_type = a.state_cd_state_code_type AND st.code = a.state_cd_state_code), A.ZIP5, TO_CHAR(A.ZIP4, 'FM0000'), a.direct_suffix, i.internal_incident, acn.common_place_name, ia.inc_addr_comment, ia.inc_addr_comment, CASE WHEN IA.BEAT IS NOT NULL AND IA.SUB_BEAT IS NOT NULL THEN IA.BEAT||'-'||IA.SUB_BEAT ELSE IA.BEAT||IA.SUB_BEAT END, IA.BEAT, IA.SUB_BEAT, IA.LOC_AGENCY_CODE, (select ag1.agency_desc from agency_codes ag1 where IA.LOC_AGENCY_CODE = ag1.agency_code), IA.CITY, (select C.description from ejs_codes C where C.code_type = IA.CITY_TYPE_CODE AND C.code = IA.CITY), IA.COUNTY_CODE, (select CNTY.description from ejs_codes CNTY where CNTY.code_type = IA.COUNTY_CODE_TYPE AND CNTY.code = IA.COUNTY_CODE), TOWNSHIP_CODE, (select TWN.description from ejs_codes TWN where TWN.code_type = IA.TOWNSHIP_CODE_TYPE AND TWN.code = IA.TOWNSHIP_CODE) FROM INCIDENTS I INNER JOIN INCIDENT_SUPPLEMENTS ISUPP ON I.INCIDENT_ID = ISUPP.INC_INCIDENT_ID LEFT OUTER JOIN INCIDENT_ADDRESSES IA ON I.INCIDENT_ID = IA.INCIDENT_ID LEFT OUTER JOIN ADDRESSES A ON IA.ADDRESS_ID = A.ADDRESS_ID LEFT OUTER JOIN ADDRESS_COMMON_NAMES ACN ON A.ADDRESS_ID = ACN.ADDR_ADDRESS_ID
 
Possibly Referenced Tables/Views:
ADDRESS_COMMON_NAMES ADDRESSES AGENCY_CODES EJS_CODES INCIDENT_ADDRESSES INCIDENT_SUPPLEMENTS INCIDENTS 


Close relationships: