Select rows that have invalid subtype/domain values using SQL

Question

Oracle 18c 10.7.1 EGDB:

In an SQL query, is there a way to select rows that have:

* Invalid subtype code values (or invalid subtype domain values)
* Or invalid coded-value domain values (non-subtype)

Bud · Answer

Here's what I came up with:

--create or replace view qc_lc_events_fields_vs_domains_vw as 
select
    objectid,
    event_id,
    field_domain_mismatch --Only displays the first problem field that's found for each row. Once the first problem is fixed, the next problem will be displayed.
from
    (
    select
        a.objectid,
        a.event_id,
        --Info about DECODE and nulls: https://blog.tuningsql.com/oracle-coding-around-null-values/#:~:text=NULL values to be equivalent
        case
            when decode(a.event_status, b.code,        'SAME', 'DIFF') = 'DIFF' then 'EVENT_STATUS'
            when decode(a.asset_class,  c.code,        'SAME', 'DIFF') = 'DIFF' then 'ASSET_CLASS'
            when decode(a.strategy,     d.code,        'SAME', 'DIFF') = 'DIFF' then 'STRATEGY'
            when decode(a.activity,     e.domain_code, 'SAME', 'DIFF') = 'DIFF' then 'ACTIVITY'
            when decode(a.project_lead, f.code,        'SAME', 'DIFF') = 'DIFF' then 'PROJECT_LEAD'
            when decode(a.side,         g.code,        'SAME', 'DIFF') = 'DIFF' then 'SIDE'
        end as field_domain_mismatch
    from
        infrastr.lc_events a
    left join
        infrastr.d_event_status_vw b
        on a.event_status = b.code
    left join
        infrastr.sub_lc_events_asset_class_vw c
        on a.asset_class = c.code
    left join
        infrastr.d_strategy_vw d
        on a.strategy = d.code
    left join
        infrastr.d_activity_lc_events_asset_class_subtype_vw e
        on a.asset_class = e.subtype_code and a.activity = e.domain_code
    left join
        infrastr.d_municipality_vw f
        on a.project_lead = f.code
    left join
        infrastr.d_direction_vw g
        on a.side = g.code
    )
where
    field_domain_mismatch is not null

Source for the domain views (the views that start with "d_" such as d_event_status_vw):

* Select domain codes/descriptions using XMLTABLE instead of EXTRACTVALUE
* The query in that link selects all domains. Whereas, for my "d_" views, I have a WHERE clause that only selects the domain data (codes & descriptions) for a specific domain.

Source for the domain view d_activity_lc_events_asset_class_subtype_vw (also has subtype columns):

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

Source for the subtype view sub_lc_events_asset_class_vw:

* Select subtype and a subtype field's domain names using SQL

* Find problem rows in GDB_ITEMS_VW
* Join to XML query is only performant with redundant hardcoded WHERE clause
* Extract specific value from XML array (where FieldName = x)
* Update Oracle GDB docs: EXTRACTVALUE is deprecated, use XMLTABLE instead (faster)

Bud · Answer

Hi Dana,

Yes. I'm hoping to use a query in SQL Developer for this. Or a database view. I edited the question to clarify.

But I suppose that same solution (an SQL query) could also be used in an ArcGIS Pro definition query or Select By Attributes — by nesting the query in a subquery:

objectid in
(select
    objectid
from
    <my query or view>)

I'm currently working on writing an SQL query. I'll let you know what I come up with.

DanaNolan · Answer

Did you mean from Oracle directly (without ArcGIS)?