WHY PERFORM THE QC: During the exchange of EST data from a local Network Program database to the central NatureServe database, a record is rejected if it has an Sname that is used in more than one locally maintained EST record.
WHAT THE QUERY DOES: This query finds ELEMENT_SUBNATIONAL records that have a Subnational Scientific Name (Sname) that is used in more than one ELEMENT_SUBNATIONAL record.
ACTION: Check the Sname, distribution data, and Concept Reference (in the related ELEMENT_GLOBAL) of each record to make sure you are maintaining only the record for the taxon accepted in your jurisdiction. Delete unnecessary EST records. If you are actively maintaining EST records in your system for taxonomic concepts your program does not actually recognize, notify Central Science staff by submitting a support ticket (in Biotics: click “Contact Support” at the bottom of any screen, select “New support ticket” and fill in the form, entering Application: Biotics, Component: Science Request).
SELECT
est.element_subnational_id,
egt.elcode_bcd elcode,
nation.iso_nation_cd nation,
subnation.subnation_code subnation,
name_cat.name_type_desc name_type,
sname.scientific_name sname,
est.s_rank srank,
d_eo_trk.eo_track_status_cd eo_trk,
(Select Count (*) From eo
Where est.element_subnational_id = eo.element_subnational_id ) eos,
/* DISTRIBUTION DATA */
DelimList('SELECT ''ORG: '' || substr(d_origin.origin_desc,1,1) ||
''; REG: '' || substr(d_reg.regularity_desc,1,1) ||
''; CNF: '' || substr(d_conf.dist_confidence_cd,1,3) ||
''; PRS: '' || substr(d_pres.curr_presence_absence_desc,1,1) || (case when
s_dist.d_population_id is not null then
''; POP: '' || substr(d_pop.population_desc,1,1) else null end)
FROM taxon_subnatl_dist s_dist, d_curr_presence_absence d_pres,
d_dist_confidence d_conf, d_origin, d_population d_pop, d_regularity d_reg
WHERE s_dist.d_curr_presence_absence_id = d_pres.d_curr_presence_absence_id (+)
and s_dist.d_dist_confidence_id = d_conf.d_dist_confidence_id (+)
and s_dist.d_origin_id = d_origin.d_origin_id (+)
and s_dist.d_population_id = d_pop.d_population_id (+)
and s_dist.d_regularity_id = d_reg.d_regularity_id (+)
and s_dist.element_subnational_id = ' || est.element_subnational_id, ' xx ') AS
distribution_data,
/* DISTRIBUTION DATA CONDENSED - NO FIELD NAMES */
DelimList('SELECT ''''|| substr(d_origin.origin_desc,1,1) ||
''. '' || substr(d_reg.regularity_desc,1,1) ||
''. '' || substr(d_conf.dist_confidence_cd,1,3) ||
''. '' || substr(d_pres.curr_presence_absence_desc,1,1) || (case when
s_dist.d_population_id is not null then
''. '' || substr(d_pop.population_desc,1,1) else null end)
FROM taxon_subnatl_dist s_dist, d_curr_presence_absence d_pres,
d_dist_confidence d_conf, d_origin, d_population d_pop, d_regularity d_reg
WHERE s_dist.d_curr_presence_absence_id = d_pres.d_curr_presence_absence_id (+)
and s_dist.d_dist_confidence_id = d_conf.d_dist_confidence_id (+)
and s_dist.d_origin_id = d_origin.d_origin_id (+)
and s_dist.d_population_id = d_pop.d_population_id (+)
and s_dist.d_regularity_id = d_reg.d_regularity_id (+)
and s_dist.element_subnational_id = ' || est.element_subnational_id, ' xx ') AS
dist_data_cond,
est.s_distribution_com est_dist_cmt,
d_maint.maintained_by_status_cd est_maint,
egt.inactive_ind inactive,
(select concept_name.scientific_name from scientific_name concept_name
where egt.concept_name_id = concept_name.scientific_name_id) as egt_name_in_concept_ref,
(select concept_ref.full_citation from reference concept_ref
where egt.concept_reference_id = concept_ref.reference_id) as egt_concept_ref
FROM element_subnational est,
element_national ent,
element_global egt,
scientific_name gname,
scientific_name sname,
nation,
subnation,
d_name_category name_cat,
d_maintained_by_status d_maint,
d_eo_track_status d_eo_trk
WHERE est.element_national_id = ent.element_national_id
and ent.element_global_id = egt.element_global_id
and egt.gname_id = gname.scientific_name_id
and gname.d_name_category_id = name_cat.d_name_category_id
and est.sname_id = sname.scientific_name_id
and est.subnation_id = subnation.subnation_id
and ent.nation_id = nation.nation_id
and est.d_maintained_by_status_id = d_maint.d_maintained_by_status_id
and est.d_eo_track_status_id = d_eo_trk.d_eo_track_status_id (+)
and name_cat.name_type_cd in ('A', 'P')
and exists
(select * from element_subnational est_other
where est_other.element_subnational_id <>
est.element_subnational_id
and est.subnation_id=est_other.subnation_id
and est_other.sname_id=est.sname_id)
ORDER BY name_cat.name_type_desc, sname.scientific_name