Select rows that have invalid subtype/domain values using SQL

123
3
06-24-2022 07:40 AM
Labels (1)
Bud
by
Regular Contributor II

Oracle 18c 10.7.1 EGDB:

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

  1. Invalid subtype code values (or invalid subtype domain values)
  2. Or invalid coded-value domain values (non-subtype)
0 Kudos
3 Replies
DanaNolan
Regular Contributor

Did you mean from Oracle directly (without ArcGIS)?

Bud
by
Regular Contributor II

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.

 

0 Kudos
Bud
by
Regular Contributor II

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%20values%20to%20be%20equivalent
        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

Bud_0-1658114012361.png


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

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

Source for the subtype view sub_lc_events_asset_class_vw:

 

Related:

 

0 Kudos