|
|
| 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 |
| INCIDENT_ID |
number |
0 |
√ |
|
null |
|
|
| INC_REPORT_NUMBER |
varchar2 |
20 |
√ |
|
null |
|
|
| AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| AGENCY_NAME |
varchar2 |
40 |
√ |
|
null |
|
|
| INCIDENT_DATE |
date |
7 |
√ |
|
null |
|
|
| STATUS |
varchar2 |
30 |
|
|
|
|
|
| SUPP_SEQ |
number |
0 |
|
|
|
|
|
| SUMMARY |
varchar2 |
1000 |
√ |
|
null |
|
|
| OFFENSE_CODE |
varchar2 |
30 |
|
|
|
|
|
| OFFENSE_DESC |
varchar2 |
255 |
√ |
|
null |
|
|
| PLACE_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| LOCATION_TYPE |
varchar2 |
4000 |
√ |
|
null |
|
|
| LONGITUDE |
varchar2 |
30 |
√ |
|
null |
|
|
| LATITUDE |
varchar2 |
30 |
√ |
|
null |
|
|
| ADDRESS_ID |
number |
0 |
|
|
|
|
|
| STREET_NUMBER |
varchar2 |
10 |
√ |
|
null |
|
|
| DIRECTIONAL |
varchar2 |
30 |
√ |
|
null |
|
|
| STREET_NAME |
varchar2 |
40 |
√ |
|
null |
|
|
| STREET_TYPE |
varchar2 |
30 |
√ |
|
null |
|
|
| DIRECT_SUFFIX |
varchar2 |
30 |
√ |
|
null |
|
|
| SUB_NUMBER |
varchar2 |
50 |
√ |
|
null |
|
|
| SUBTYPE_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| CITY |
varchar2 |
40 |
√ |
|
null |
|
|
| GEO_CITY |
varchar2 |
40 |
√ |
|
null |
|
|
| STATE |
varchar2 |
30 |
√ |
|
null |
|
|
| ZIP5 |
varchar2 |
30 |
√ |
|
null |
|
|
| ZIP4 |
number |
0 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT distinct i.incident_id incident_id,
i.inc_report_number inc_report_number,
isupp.supp_agency_code as agency_code,
ac.Agency_Desc as agency_desc,
i.start_time incident_date,
isupp.isc_status_code status,
isupp.supp_seq supp_seq,
-- isupp.agency_only agency_only,
i.summary summary,
o.offns_cd_offense_code offense_code,
(SELECT OFFENSE_DESC
FROM OFFENSE_CODES oc
WHERE oc.OFFENSE_CODE=o.offns_cd_offense_code
) AS Offense_Desc,
o.place_place_code place_code,
(SELECT Description
FROM EJS_CODES ecs
WHERE CODE_TYPE='PLACE_CODES'
AND ecs.code=O.PLACE_PLACE_CODE) AS Location_Type,
a.longitude,
a.latitude,
a.address_id,
a.street_number,
a.dirct_cd_direction_code as directional,
a.street_name,
a.street_cd_street_type_code as Street_Type,
a.direct_suffix,
a.sub_number,
a.addr_sc_address_subtype_code as subtype_code,
INITCAP(a.city) as city,
INITCAP(a.geo_city) as geo_city,
a.state_cd_state_code as state,
a.zip5,
a.zip4
FROM offenses o,
incidents i,
incident_supplements isupp,
addresses a,
incident_addresses ia,
agency_codes ac
WHERE o.inc_incident_id=i.incident_id
and o.UCR_NUMBER=1
and isupp.inc_incident_id=i.incident_id
and ia.incident_id=i.incident_id(+)
and a.address_id=ia.address_id(+)
and ac.agency_code=isupp.supp_agency_code
Possibly Referenced Tables/Views:
