This query checks for inconsistencies in the SRANK of infra taxa and their related “parent” species. 

Example: Infra SRANK = S4 and parent SRANK = SH. 

ACTION: Please review both the SRANKS and GRANKS for these elements, then update the SRANK values in the infra and/or the “parent” species as needed.

SELECT nation.iso_nation_cd nation,

subnation.subnation_code subnation,

ps_sn.subnation_code parent_sn,

name_cat.name_category_desc name_category,

egt.element_global_id egt_id,

egt.elcode_bcd elcode,

gname.scientific_name gname,

egt.g_primary_common_name gcomname,

tg.parent_species_id ps_egt_id,

ps_egt.elcode_bcd parent_elcode,

ps_gname.scientific_name parent_gname,

ps_egt.g_primary_common_name parent_gcomname,

est.element_subnational_id est_id,

sname.scientific_name sname,

est.s_primary_common_name scomname,

egt.g_rank,

egt.rounded_g_rank,

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

ps_egt.g_rank parent_grank,

ps_egt.rounded_g_rank parent_rounded_grank,

to_char(ps_egt.g_rank_change_date, 'YYYY-MM-DD') parent_grank_chng_dt,

est.s_rank,

est.rounded_s_rank,

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

ps_est.s_rank parent_srank,

ps_est.rounded_s_rank parent_rounded_srank,

to_char(ps_est.s_rank_change_date, 'YYYY-MM-DD') parent_srank_chng_dt,

est.s_element_internal_notes est_note

FROM element_global egt, element_national ent, element_subnational est,

element_global ps_egt, element_national ps_ent, element_subnational ps_est,

taxon_global tg,

scientific_name gname,

scientific_name ps_gname,

scientific_name sname,

nation, subnation, subnation ps_sn,

d_name_category name_cat

WHERE egt.inactive_ind = 'N'

and 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 egt.element_global_id = tg.element_global_id

and ent.nation_id = nation.nation_id

and est.subnation_id = subnation.subnation_id

and ps_est.subnation_id = ps_sn.subnation_id

and est.sname_id = sname.scientific_name_id

and tg.parent_species_id = ps_egt.element_global_id

and ps_ent.element_global_id = ps_egt.element_global_id

and ps_est.element_national_id = ps_ent.element_national_id

and ps_egt.gname_id = ps_gname.scientific_name_id

/* HAS A PARENT SPECIES */

and tg.parent_species_id is not null

and

/* SRANK CONFLICT WITH PARENT SRANK */

(

(

(/* both are numbers and SRANK lower than PARENT SRANK */

( ps_est.rounded_s_rank like '_1'

or ps_est.rounded_s_rank like '_2'

or ps_est.rounded_s_rank like '_3'

or ps_est.rounded_s_rank like '_4'

or ps_est.rounded_s_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(ps_est.rounded_s_rank,2,1))

)

or (/* numeric SRANKs when PARENT SRANK is 'SX' or 'SH'

-- alpha characters are greater than numeric characters in strings */

(ps_est.rounded_s_rank like '_X' or

ps_est.rounded_s_rank like '_H')

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

)

)

And subnation.subnation_code = ps_sn.subnation_code