This query selects species EO with an SRANK of "SX" or "SH" and with an EORANK value that is NOT “X”, “H”, or “F” (or is null). This appears to be a discrepancy between the information in the ELEMENT_SUBNATIONAL and EO records.

UPDATED 10/6/2016: Added “ID confirmed” to the fields displayed.

UPDATED 9/26/2016: Added “EO Origin subrank” to the fields displayed.

ACTION: Review the SRANK, EORANK, and LAST_OBS_DATE values, then update as needed.

SELECT decode(ent.nation_id, 38, 'CA', 225, 'US') nation,

subnation.subnation_code subnation,

eo.eo_ou_uid, eo.eo_seq_uid,

eo.eo_num, id.id_confirmed_cd,

dlookup('D_NAME_CATEGORY', gname.d_name_category_id) name_category_desc,

egt.element_global_id,

gname.scientific_name gname, egt.g_primary_common_name gcomname,

est.rounded_s_rank,

substr(est.s_rank_change_date,1,10) SRANK_CHANGE_DATE,

substr(est.s_rank_review_date,1,10) SRANK_REVIEW_DATE,

eorank.basic_eo_rank_cd eo_rank,

eo.eo_rank_date,

subrank.display_value EO_origin,

eo.LAST_OBS_DATE,

substr(eo.last_obs_date,1,4) last_year

FROM eo,

element_subnational est,

element_national ent,

element_global egt,

subnation,

d_basic_eo_rank eorank,

scientific_name gname,

d_origin_subrank subrank,

d_id_confirmed id

WHERE eo.element_subnational_id = est.element_subnational_id

and est.element_national_id = ent.element_national_id

and ent.element_global_id = egt.element_global_id

and est.subnation_id = subnation.subnation_id

and egt.gname_id = gname.scientific_name_id

and eo.d_basic_eo_rank_id = eorank.d_basic_eo_rank_id (+)

and eo.d_origin_subrank_id = subrank.d_origin_subrank_id (+)

and eo.d_id_confirmed_id = id.d_id_confirmed_id (+)

and (est.rounded_s_rank in ('SH', 'SX')

and (eorank.basic_eo_rank_cd is null

or eorank.basic_eo_rank_cd not in ('X', 'X?', 'H', 'H?', 'F')))

/* excludes inactive EGT */

and egt.inactive_ind = 'N'

/* botanical or zoological */

and gname.d_name_category_id in (1, 2, 3, 4, 5, 6, 7, 19)

/* excludes sub EOs */

and eo.principal_eo_shape_id is null