View slot9.WORLD.TSTA.INCIDENT_GIS_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  √  null
INCIDENTS.INCIDENT_ID Implied Constraint R
INC_REPORT_NUMBER varchar2 20  √  null
AGENCY_CODE varchar2 30  √  null
AGENCY_CODES.AGENCY_CODE Implied Constraint R
REPORT_DATE date 7  √  null
START_DATE date 7  √  null
END_DATE date 7  √  null
STATUS varchar2 30  √  null
SUPP_SEQ number 0  √  null
RESPONSIBLE_USER_ID varchar2 100  √  null
AGENCY_ONLY varchar2 1  √  null
CID varchar2 1  √  null
SECURITY_LEVEL number 38  √  null
SUMMARY varchar2 1000  √  null
ADDRESS_ID number 0  √  null
ADDRESSES.ADDRESS_ID Implied Constraint R
STREET_NUMBER varchar2 10  √  null
DIRECTION_CODE varchar2 30  √  null
STREET_NAME varchar2 40  √  null
STREET_CD_STREET_TYPE_CODE varchar2 30  √  null
ADDR_SC_ADDRESS_SUBTYPE_CODE varchar2 30  √  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_DIR_SUFFIX varchar2 30  √  null
INC_ADDR_COMMENT varchar2 255  √  null
BEAT varchar2 30  √  null
SUB_BEAT varchar2 30  √  null
LONGITUDE varchar2 30  √  null
LATITUDE varchar2 30  √  null
CITY varchar2 40  √  null
GEO_CITY varchar2 40  √  null
STATE_CD_STATE_CODE varchar2 30  √  null
ZIP5 varchar2 30  √  null
ZIP4 number 0  √  null
DIRECT_SUFFIX varchar2 30  √  null
OFFENSES varchar2 4000  √  null
LAST_NAME varchar2 100  √  null
FIRST_NAME varchar2 80  √  null
ROLE_TYPE varchar2 4000  √  null

Analyzed at Mon Sep 20 21:05 MDT 2021

View Definition:
SELECT i.incident_id, i.inc_report_number, isupp.supp_agency_code "AGNCY_CD_AGENCY_CODE", i.report_date, i.start_date, i.end_date, isupp.isc_status_code "STATUS", isupp.supp_seq, isupp.responsible_user_id, isupp.agency_only, isupp.cid, isupp.security_level, i.summary, a.address_id, a.street_number, a.dirct_cd_direction_code, a.street_name, a.street_cd_street_type_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, a.intrsect1_dir_suffix, ia.inc_addr_comment, a.beat, a.sub_beat, a.longitude, a.latitude, a.city, a.geo_city, a.state_cd_state_code, a.zip5, a.zip4, a.direct_suffix, get_inc_offense_lst(i.incident_id, NULL) offenses, mn.lname, mn.fname, get_inc_person_role_lst(ip.inc_per_id, NULL) inc_roles FROM addresses a, incident_addresses ia, incidents i, incident_supplements isupp, incident_people ip, master_names mn WHERE isupp.inc_incident_id = i.incident_id AND ia.incident_id = i.incident_id AND ia.address_id = a.address_id AND i.creator_date > (SELECT TO_DATE (esm.VALUE, 'MM-DD-YYYY HH24:MI:SS') FROM ejs_sub_maint esm WHERE esm.keyword = 'GIS_START_DATE_FROM') AND isupp.inc_incident_id = ip.inc_incident_id AND isupp.supp_seq = ip.supp_seq AND ip.per_person_id = mn.per_person_id AND ip.mn_mn_id = mn.mn_id -- UNION -- SELECT i.incident_id, i.inc_report_number, isupp.supp_agency_code "AGNCY_CD_AGENCY_CODE", i.report_date, i.start_date, i.end_date, isupp.isc_status_code "STATUS", isupp.supp_seq, isupp.responsible_user_id, isupp.agency_only, isupp.cid, isupp.security_level, i.summary, a.address_id, a.street_number, a.dirct_cd_direction_code, a.street_name, a.street_cd_street_type_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, a.intrsect1_dir_suffix, ia.inc_addr_comment, a.beat, a.sub_beat, a.longitude, a.latitude, a.city, a.geo_city, a.state_cd_state_code, a.zip5, a.zip4, a.direct_suffix, get_inc_offense_lst(i.incident_id, NULL) offenses, b.BUSINESS_NAME, '', ib.ib1_role_type FROM addresses a, incident_addresses ia, incidents i, incident_supplements isupp, incident_businesses ib, businesses b WHERE isupp.inc_incident_id = i.incident_id AND ia.incident_id = i.incident_id AND ia.address_id = a.address_id AND i.creator_date > (SELECT TO_DATE (esm.VALUE, 'MM-DD-YYYY HH24:MI:SS') FROM ejs_sub_maint esm WHERE esm.keyword = 'GIS_START_DATE_FROM') AND isupp.inc_incident_id = ib.inc_incident_id AND isupp.supp_seq = ib.supp_seq AND ib.busns_business_number = b.business_number -- UNION -- no people AND no businesses SELECT i.incident_id, i.inc_report_number, isupp.supp_agency_code "AGNCY_CD_AGENCY_CODE", i.report_date, i.start_date, i.end_date, isupp.isc_status_code "STATUS", isupp.supp_seq, isupp.responsible_user_id, isupp.agency_only, isupp.cid, isupp.security_level, i.summary, a.address_id, a.street_number, a.dirct_cd_direction_code, a.street_name, a.street_cd_street_type_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, a.intrsect1_dir_suffix, ia.inc_addr_comment, a.beat, a.sub_beat, a.longitude, a.latitude, a.city, a.geo_city, a.state_cd_state_code, a.zip5, a.zip4, a.direct_suffix, get_inc_offense_lst(i.incident_id, NULL) offenses, '', '', '' FROM addresses a, incident_addresses ia, incidents i, incident_supplements isupp WHERE isupp.inc_incident_id = i.incident_id AND ia.incident_id = i.incident_id AND ia.address_id = a.address_id AND i.creator_date > (SELECT TO_DATE (esm.VALUE, 'MM-DD-YYYY HH24:MI:SS') FROM ejs_sub_maint esm WHERE esm.keyword = 'GIS_START_DATE_FROM') AND 0 = ( SELECT COUNT(ip.inc_per_id ) FROM incident_people ip ,incident_supplements supp WHERE ip.inc_incident_id = i.incident_id AND ip.inc_incident_id = supp.inc_incident_id AND ip.supp_seq = supp.supp_seq ) AND 0 = ( SELECT COUNT ( ib.busns_business_number ) FROM incident_businesses ib WHERE ib.inc_incident_id = isupp.inc_incident_id AND ib.supp_seq = isupp.supp_seq )
 
Possibly Referenced Tables/Views:
ADDRESSES BUSINESSES EJS_SUB_MAINT INCIDENT_ADDRESSES INCIDENT_BUSINESSES INCIDENT_PEOPLE INCIDENT_SUPPLEMENTS INCIDENTS MASTER_NAMES OFFENSES PEOPLE 


Close relationships: