|
|
| 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 |
| USER_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| USER_NAME |
varchar2 |
41 |
√ |
|
null |
|
|
| BADGE_NUMBER |
varchar2 |
100 |
√ |
|
null |
|
|
| AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| AGENCY_NAME |
varchar2 |
40 |
√ |
|
null |
|
|
| ATTACHMENT_TYPE |
varchar2 |
10 |
√ |
|
null |
|
|
| ATTACHMENT_ID |
number |
0 |
√ |
|
null |
|
|
| ATTACHMENT_BYTES |
number |
0 |
√ |
|
null |
|
|
| FILE_TYPE |
varchar2 |
6 |
√ |
|
null |
|
|
| CREATOR_DATE |
date |
7 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT
ATT.CREATOR_ID AS USER_ID,
IJIS.FNAME || ' ' || IJIS.LNAME AS USER_NAME,
OFFS.INTERNAL_ID AS BADGE_NUMNBER,
NVL(IJIS.AGNCY_CD_AGENCY_CODE, 'UNKNOWN') AS AGENCY_CODE,
NVL(AC.AGENCY_DESC, 'UNKNOWN') AS AGENCY_NAME,
'ATTACHMENT' AS ATTACHMENT_TYPE,
ATT.ID AS ATTACHMENT_ID,
NVL(DBMS_LOB.GETLENGTH(ATT.MS_OBJ),0) AS ATTACHMENT_BYTES,
FILE_TYPE,
ATT.CREATOR_DATE
FROM ATTACHMENTS ATT
LEFT OUTER JOIN IJIS_USER_PROFILES IJIS ON IJIS.LOGIN_ID = ATT.CREATOR_ID
LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = IJIS.AGNCY_CD_AGENCY_CODE
LEFT OUTER JOIN OFFICERS OFFS ON OFFS.USER_ID = IJIS.LOGIN_ID
UNION
SELECT
IMG.CREATOR_ID AS USER_ID,
IJIS.FNAME || ' ' || IJIS.LNAME AS USER_NAME,
OFFS.INTERNAL_ID AS BADGE_NUMNBER,
NVL(IJIS.AGNCY_CD_AGENCY_CODE, 'UNKNOWN') AS AGENCY_CODE,
NVL(AC.AGENCY_DESC, 'UNKNOWN') AS AGENCY_NAME,
'IMAGE' AS ATTACHMENT_TYPE,
IMG.IMAGE_ID AS ATTACHMENT_ID,
NVL(DBMS_LOB.GETLENGTH(IMG.IMAGE),0) AS ATTACHMENT_BYTES,
FILE_TYPE,
IMG.CREATOR_DATE
FROM IMAGES IMG
LEFT OUTER JOIN IJIS_USER_PROFILES IJIS ON IJIS.LOGIN_ID = IMG.CREATOR_ID
LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = IJIS.AGNCY_CD_AGENCY_CODE
LEFT OUTER JOIN OFFICERS OFFS ON OFFS.USER_ID = IJIS.LOGIN_ID
UNION
SELECT
IMG.CREATOR_ID AS USER_ID,
IJIS.FNAME || ' ' || IJIS.LNAME AS USER_NAME,
OFFS.INTERNAL_ID AS BADGE_NUMNBER,
NVL(IJIS.AGNCY_CD_AGENCY_CODE, 'UNKNOWN') AS AGENCY_CODE,
NVL(AC.AGENCY_DESC, 'UNKNOWN') AS AGENCY_NAME,
'THUMBNAIL' AS ATTACHMENT_TYPE,
IMG.IMAGE_ID AS ATTACHMENT_ID,
NVL(DBMS_LOB.GETLENGTH(IMG.THUMBNAIL),0) AS ATTACHMENT_BYTES,
FILE_TYPE,
IMG.CREATOR_DATE
FROM IMAGES IMG
LEFT OUTER JOIN IJIS_USER_PROFILES IJIS ON IJIS.LOGIN_ID = IMG.CREATOR_ID
LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = IJIS.AGNCY_CD_AGENCY_CODE
LEFT OUTER JOIN OFFICERS OFFS ON OFFS.USER_ID = IJIS.LOGIN_ID
Possibly Referenced Tables/Views:
