WHY PERFORM THE QC: During the exchange of ELEMENT_SUBNATIONAL data from a local Network Program database to the central NatureServe database, a record is rejected if (a) the SRANK is a number that is 3 or more greater than the number value of the GRANK or the NRANK, (b) if the SRANK is a number rank of 2 or more and the GRANK is GH or GX, or the NRANK is NH or NX.

WHAT THE QUERY DOES: This query finds ELEMENT_SUBNATIONAL records where the SRANK is significantly lower in conservation value than indicated by the GRANK or NRANK (SRANK is higher in number).

ACTION: Check that the SRANK entered is correct. If not, update the rank. If you believe the Srank is correct and that the Nrank and/or Grank need to be changed, notify Central Science Staff by creating a support ticket with the recommendation (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 name_cat.name_type_desc name_type,

informal_tax(egt.element_global_id) informal_tax_grp, 

egt.elcode_bcd elcode,

egt.g_rank grank,

egt.rounded_g_rank rnd_grank,

to_char(egt.g_rank_change_date, 'YYYY-MM-DD') grank_chng_dt,

ent.n_rank nrank,

ent.rounded_n_rank rnd_nrank,

to_char(ent.n_rank_change_date, 'YYYY-MM-DD') nrank_chng_dt,

est.s_rank srank,

est.rounded_s_rank rnd_srank,

d_eo_trk.eo_track_status_cd eo_trk,

(Select Count (*) From eo

 Where est.element_subnational_id = eo.element_subnational_id ) eos,

to_char(est.s_rank_change_date, 'YYYY-MM-DD') srank_chng_dt,

gname.scientific_name gname,

nname.scientific_name nname,

sname.scientific_name sname,

maint.maintained_by_status_cd maint_by,

egt.inactive_ind inactive,

nation.iso_nation_cd nation,

subnation.subnation_code subnation,

est.element_subnational_id,

est.element_subnational_ou_uid,

est.element_subnational_seq_uid,

sname.scientific_name_ou_uid sname_ou_uid,

sname.scientific_name_seq_uid sname_seq_uid,

egt.element_global_id

FROM element_global egt,

element_national ent,

element_subnational est,

scientific_name gname,

scientific_name nname,

scientific_name sname,

nation,

subnation,

d_name_category name_cat,

d_maintained_by_status 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 ent.nname_id = nname.scientific_name_id

and est.sname_id = sname.scientific_name_id

and ent.nation_id = nation.nation_id

and est.subnation_id = subnation.subnation_id

and est.d_maintained_by_status_id = maint.d_maintained_by_status_id

and est.d_eo_track_status_id = d_eo_trk.d_eo_track_status_id (+)

and 

( 

 /* SRANK CONFLICT WITH GRANK */ 

 (

 /* both ranks are numbers and SRANK SRANK higher in number than GRANK by 3 or more */ 

 (

 (egt.rounded_g_rank like '_1'

 or egt.rounded_g_rank like '_2'

 or egt.rounded_g_rank like '_3'

 or egt.rounded_g_rank like '_4'

 or egt.rounded_g_rank like '_5') 

 and

 (est.rounded_s_rank like '_1'

 or est.rounded_s_rank like '_2'

 or est.rounded_s_rank like '_3'

 or est.rounded_s_rank like '_4'

 or est.rounded_s_rank like '_5') 

 and

 to_number(substr(est.rounded_s_rank,2,1)) >= (to_number(substr(egt.rounded_g_rank,2,1)) + 3)

 )

 OR

 /* SRANK is a number of 2 or more and GRANK is 'GX' or 'GH' */ 

 (

 (egt.rounded_g_rank like '_X' or

 egt.rounded_g_rank like '_H')

 and

 (est.rounded_s_rank like '_1'

 or est.rounded_s_rank like '_2'

 or est.rounded_s_rank like '_3'

 or est.rounded_s_rank like '_4'

 or est.rounded_s_rank like '_5') 

 and (to_number(substr(est.rounded_s_rank,2,1)) > 1)

 )

 ) 

 OR

 /* SRANK CONFLICT WITH NRANK */

 (

 /* both are numbers and SRANK higher in number than NRANK by 3 or more */ 

 (

 (ent.rounded_n_rank like '_1'

 or ent.rounded_n_rank like '_2'

 or ent.rounded_n_rank like '_3'

 or ent.rounded_n_rank like '_4'

 or ent.rounded_n_rank like '_5') 

 and

 (est.rounded_s_rank like '_1'

 or est.rounded_s_rank like '_2'

 or est.rounded_s_rank like '_3'

 or est.rounded_s_rank like '_4'

 or est.rounded_s_rank like '_5') 

 and to_number(substr(est.rounded_s_rank,2,1)) >= (to_number(substr(ent.rounded_n_rank,2,1)) + 3)

 )

 OR

 /* SRANK is a number of 2 or more when NRANK is 'NX' or 'NH' */ 

 (

 (ent.rounded_n_rank like '_X' or

 ent.rounded_n_rank like '_H')

 and

 (est.rounded_s_rank like '_1'

 or est.rounded_s_rank like '_2'

 or est.rounded_s_rank like '_3'

 or est.rounded_s_rank like '_4'

 or est.rounded_s_rank like '_5')

 and (to_number(substr(est.rounded_s_rank,2,1)) > 1)

 )

 )

)

order by name_cat.name_type_desc,

egt.rounded_g_rank,

informal_tax(egt.element_global_id),

sname.scientific_name