This query finds all ELEMENT_SUBNATIONAL records for SPECIES that have an EO, and the DISTRIBUTION CONFIDENCE is any value other than “confident”: 

  • NT (Never was there)
  • P (Potential)
  • PRF (Potential but false report exists)
  • R (Reported but unconfirmed)
  • RD (Reported but doubtful)
  • RF (Reported but false)

 

ACTION: Please update information in the ELEMENT_SUBNATIONAL distribution grid and/or update information for the associated EO records.

 

SELECT

egt.element_global_id egt_id,

est.element_subnational_id est_id,

egt.elcode_bcd,

nation.iso_nation_cd natn,

subnation.subnation_code subn,

(select clstat.classification_status_desc from d_classification_status clstat

where clstat.d_classification_status_id = egt.d_classification_status_id) class,

(select categ.name_category_desc from d_name_category categ where categ.d_name_category_id = sn.d_name_category_id) category,

sn.d_name_category_id,

gname.scientific_name gname,

sn.scientific_name sname,

est.s_primary_common_name scomname,

egt.g_rank,

egt.rounded_g_rank rnd,

est.s_rank srank,

d_dist_confidence.dist_confidence_desc dist_confidence,

(SELECT d_eo_track_status.eo_track_status_cd FROM d_eo_track_status

WHERE est.d_eo_track_status_id = d_eo_track_status.d_eo_track_status_id ) eo_track,

(SELECT COUNT (*)

FROM EO

WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID ) AS tot_EOS,

(SELECT COUNT (*)

FROM EO

WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID

and (eo.d_id_confirmed_id is null or eo.d_id_confirmed_id != 1) ) AS eos_w_ident_conf_y_ques_null,

(SELECT COUNT (*)

FROM EO

WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID

and eo.d_id_confirmed_id = 1) AS eos_w_ident_conf_n,

DelimList ('SELECT DISTINCT d_county.county_name AS counties '

|| ' FROM eo_county, d_county, eo

WHERE eo_county.d_county_id = d_county.d_county_id

and eo_county.eo_id = eo.eo_id

and eo.eo_id not in

(select eofalsetc.eo_id from eo eofalsetc, taxon_subnatl_dist tsdfalsetc

where tsdfalsetc.element_subnational_id = eofalsetc.element_subnational_id

and tsdfalsetc.d_dist_confidence_id in (2, 3, 4, 5, 6, 7) /**A TSD row has Distribution Confidence OTHER than Confident***/

and eofalsetc.d_id_confirmed_id = 1 /**EO Identification Confirmed N**/

and not exists /***No TSD row with Distribution Confidence of Confident***/

(select * from taxon_subnatl_dist tsd where eofalsetc.element_subnational_id = tsd.element_subnational_id and tsd.d_dist_confidence_id = 1))

and eo.element_subnational_id = ' || est.element_subnational_id || 'ORDER BY d_county.county_name ', ', ') AS counties_this_subn,

DelimList('SELECT ''ORG: '' || substr(d_origin.origin_desc,1,3) ||

''; REG: '' || substr(d_reg.regularity_desc,1,3) ||

''; CNF: '' || substr(d_conf.display_value,1,2) ||

''; PRS: '' || substr(d_pres.curr_presence_absence_desc,1,3) || (case when s_dist.d_population_id is not null then

''; POP: '' || substr(d_pop.population_desc,1,3) 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, '; ') AS taxon_subnatl_dist,

to_char(est.s_rank_change_date,'yyyy-mm-dd') srank_ch,

to_char(est.s_rank_review_date,'yyyy-mm-dd') srank_rev,

egt.element_global_seq_uid egt_seq_uid,

egt.element_global_ou_uid egt_ou_uid,

est.element_subnational_seq_uid est_seq_uid,

est.element_subnational_ou_uid est_ou_uid

FROM element_global egt,

element_national ent,

element_subnational est,

nation,

subnation,

scientific_name gname,

scientific_name sn,

taxon_subnatl_dist tsd,

d_dist_confidence

WHERE

egt.element_global_id = ent.element_global_id

and ent.element_national_id = est.element_national_id

and egt.gname_id = gname.scientific_name_id

and est.sname_id = sn.scientific_name_id

and ent.nation_id = nation.nation_id

and est.subnation_id = subnation.subnation_id

and est.element_subnational_id = tsd.element_subnational_id

and tsd.d_dist_confidence_id = d_dist_confidence.d_dist_confidence_id

and sn.d_name_category_id in (1, 2, 3, 4, 5, 6, 7, 19) /**zoological or botanical**/

and tsd.d_dist_confidence_id != 1 /***a tsd row has distribution confidence other than confident***/

and /****no tsd row has distribution confidence of confident***/

not exists (select * from taxon_subnatl_dist

where taxon_subnatl_dist.element_subnational_id = est.element_subnational_id

and taxon_subnatl_dist.d_dist_confidence_id = 1 /**confident**/)

and /***there is an eo with id confirmed null, Y, or ?***/

exists ( select * from eo

where est.element_subnational_id = eo.element_subnational_id

and (eo.d_id_confirmed_id is null or eo.d_id_confirmed_id != 1) )

ORDER BY

sn.d_name_category_id,

sn.scientific_name