|
|
| 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 |
| AGENCY_CODE |
varchar2 |
30 |
|
|
|
|
|
| PARENT_AGENCY_CODE |
varchar2 |
4000 |
√ |
|
null |
|
|
| ANCESTOR_LEVEL |
number |
0 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
WITH
PARENT_VIEW AS
(SELECT
POD.AGENCY_CODE,
PPOD.AGENCY_CODE AS PARENT_AGENCY_CODE
FROM PSEUDO_ORG_DESCRIPTORS POD
INNER JOIN PSEUDO_ORG_DESCRIPTORS PPOD ON POD.AGENCY_PARENT = PPOD.POD_ID
WHERE POD.AGENCY_PARENT != POD.POD_ID)
SELECT AGENCY_CODE,
SUBSTR(parents,1,instr(parents,'.')-1) PARENT_AGENCY_CODE,
AG_LEVEL
FROM (
SELECT APV.AGENCY_CODE,
LTRIM(SYS_CONNECT_BY_PATH(APV.PARENT_AGENCY_CODE,'.'),'.')||'.' PARENTS,
LEVEL AS AG_LEVEL
FROM
PARENT_VIEW APV
CONNECT BY PRIOR APV.AGENCY_CODE = APV.PARENT_AGENCY_CODE
ORDER BY AGENCY_CODE
)
Possibly Referenced Tables/Views:
