1

Oracle 18c 10.7.1 EGDB

For a given table/subtype:

enter image description here

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:

enter image description here

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:

enter image description here

I'm not referring to the true Subtype Field:

enter image description here

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

1 Answer 1

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).

enter image description here


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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.