For each domain of a subtype field, select the domain data and subtype data (using SQL)

Question

Oracle 18c 10.7.1 EGDB

For a given table/subtype:

For each domain of a subtype field, select the domain data and the subtype data:

• Domain code
• Domain description
• Domain name
• Subtype code
• Subtype description
• Subtype field
• Subtype field domain name
• Table name

It would look like this:

Question:

How can I do that using SQL?

---

Note:

When I say subtype field, I mean the fields in the Default Values and Domains: section:

I'm not referring to the true Subtype Field:

Apologies if that's confusing. There seem to be two different concepts of Subtype Fields.

Answer 1

1 - Subtype View:

Create a subtype view that selects these columns from the table's subtype:

• Subtype code
• Subtype description
• Subtype field
• Subtype field domain name
• Table name

The subtype view is called sub_lc_events_asset_class_activity_vw.

The SQL can be found here: Select subtype and a subtype field/domain names using SQL

---

2 - Domain View:

Create a domain view that selects these columns from all coded value domains in the GDB:

(We don't need to filter the domains because the join in the next step will do the filtering for us — via the hardcoded subtype view.)

• Domain code
• Domain description
• Domain name

The domain view is called d_all_coded_value_domains_vw.

The SQL can be found here: Select domain codes/descriptions using XMLTABLE instead of EXTRACTVALUE

---

3 - Final Query:

Create a query that selects from the subtype view and joins to the domain view via the domain name.

--create or replace view d_activity_lc_events_asset_class_subtype_vw as (
--select * from (
with
subtype as (select * from sub_lc_events_asset_class_activity_vw),
domain  as (select code, description, domain_name from d_all_coded_value_domains_vw) 
--Don't include ROWNUM column from the domain view. Makes query slow: https://stackoverflow.com/q/72985632/5576771
           
select
    cast(rownum as number(38,0)) as rownum_,
    d.code as domain_code,
    d.description as domain_description,
    d.domain_name,
    s.subtype_code,
    s.subtype_description,
    s.subtype_field,
    s.subtype_field_domain,
    s.table_name as table_name
from
    subtype s
left join
    domain d
    on s.subtype_field_domain = d.domain_name
--))

In other words, the query selects a table's subtype codes (and other columns), and joins to the subtype field domains' data (domain codes & descriptions).

• The common key is the domain name.
• The join is 1:M. It propagates-out multiple rows for each subtype(1) —> domain data(M).

---

Edit:

I'm having a problem when I create a view from the final query. The view is slow in ArcGIS Pro, but not in SQL Developer.

Query becomes slow when used in a view

I'm able to workaround the issue by bringing the SQL from the domain view (WITH clause) into the main query as a subquery.

create or replace view d_activity_lc_events_asset_class_subtype_vw as 
select
    cast(rownum as number(38,0)) as rownum_,
    d.code as domain_code,
    d.description as domain_description,
    d.domain_name,
    s.subtype_code,
    s.subtype_description,
    s.subtype_field,
    s.subtype_field_domain,
    s.table_name
from
    sub_lc_events_asset_class_activity_vw s
left join
    (
    select      
        --cast(rownum as number(38,0)) as rownum_, --https://stackoverflow.com/a/72986329/5576771
        x.code,
        x.description,
        i.name as domain_name
    from        
        sde.gdb_items_vw i
    cross apply xmltable(
        '/GPCodedValueDomain2/CodedValues/CodedValue' 
        passing xmltype(i.definition)
        columns
            code        varchar2(255) path './Code',
            description varchar2(255) path './Name'
        ) x    
    where      
        i.name is not null
    ) d
    on s.subtype_field_domain = d.domain_name

That seems to work; the view is now fast in ArcGIS Pro.

But I still don't understand what the root cause of the problem is. I don't understand why selecting the view in SQL Developer is fast, but the same thing is slow in ArcGIS Pro. Is ArcGIS Pro messing up the query somehow — maybe by wrapping it in an outer query of some sort? I tried looking at the Diagnostic Monitor logs in ArcGIS Pro (CTRL+ALT+M), but the logs in ArcGIS Pro 2.6.8 seem to be limited; I can't figure out how to view the SQL logs. Whereas I can do that in Diagnostic Monitor in ArcGIS Pro 2.9.2.

View SQL log in ArcMon (Pro 2.6.8)