SELECT 
element_subnational.element_subnational_id,
scientific_name.scientific_name sname,
subnation.subnation_code subn,
element_subnational.s_rank,
to_char (element_subnational.s_rank_change_date, 'yyyy-mm-dd') srank_ch_d,
to_char (element_subnational.s_rank_review_date, 'yyyy-mm-dd') srank_rv_d,
d_eo_track_status.eo_track_status_cd eo_track,
substr(est_origin.origin_desc, 1,4) ORIG,
substr (est_regularity.regularity_desc, 1,4) REG, 
est_confidence.dist_confidence_cd CONF, 
substr (est_presence.curr_presence_absence_desc, 1,4) PRES,
(select count (*)
       from eo
       where element_subnational.element_subnational_id = eo.element_subnational_id ) as count_of_eos, 
ELEMENT_SUBNATL_RANK.S_RANK_REASONS,
    d_number_eos.NUMBER_EOS_CD NUM_EOS,
ELEMENT_SUBNATL_RANK.S_NUMBER_EOS_COM,
    d_number_good_eos.NUMBER_GOOD_EOS_CD NUM_GOOD_EOS, 
ELEMENT_SUBNATL_RANK.S_NUMBER_GOOD_EOS_COM,
    d_number_prot_eos.NUMBER_PROT_EOS_CD NUM_PROT_EOS, 
ELEMENT_SUBNATL_RANK.S_NUMBER_PROT_EOS_COM,
    d_pop_size.POP_SIZE_CD POP_SIZE,
ELEMENT_SUBNATL_RANK.S_POP_SIZE_COM,
    d_range_extent.RANGE_EXTENT_CD RANGE_EXTENT,
dbms_lob.substr (ELEMENT_SUBNATL_RANK.S_RANGE_COM, 4000, 1) s_range_com,
    d_area_of_occupancy.AREA_OF_OCCUPANCY_CD AREA_OF_OCCUPANCY,
    d_lin_dist_of_occupancy.LIN_DIST_OF_OCCUPANCY_CD LIN_DIST_OF_OCCUPANCY,
ELEMENT_SUBNATL_RANK.S_AREA_DIST_OF_OCCUPANCY_COM,
    d_short_term_trend.SHORT_TERM_TREND_CD SHORT_TERM_TREND,
ELEMENT_SUBNATL_RANK.S_SHORT_TERM_TREND_COM,
    d_long_term_trend.LONG_TERM_TREND_CD LONG_TERM_TREND, 
ELEMENT_SUBNATL_RANK.S_LONG_TERM_TREND_COM,
D_THREAT_SCOPE.THREAT_SCOPE_DESC,
D_THREAT_SEVERITY.THREAT_SEVERITY_DESC,
D_THREAT_IMMEDIACY.THREAT_IMMEDIACY_DESC,
D_THREAT.THREAT_CD OVERALL_THREAT,
dbms_lob.substr (ELEMENT_SUBNATL_RANK.S_THREAT_COM, 4000, 1) s_threat_com,
    d_intrin_vulnerability.INTRIN_VULNERABILITY_CD INTRIN_VULNERABILITY,
ELEMENT_SUBNATL_RANK.S_INTRINSIC_VULNERABILITY_COM,
    d_enviro_specificity.ENVIRO_SPECIFICITY_CD ENVIRO_SPECIFICITY,
ELEMENT_SUBNATL_RANK.S_ENVIRO_SPECIFICITY_COM,
dbms_lob.substr (ELEMENT_SUBNATL_RANK.S_OTHER_RANK_CONSID_COM, 4000, 1) s_other_consid,
dbms_lob.substr (ELEMENT_SUBNATL_RANK.S_RESEARCH_NEEDS, 4000, 1) s_research_needs,
ELEMENT_SUBNATL_RANK.S_INVENTORY_NEEDS,
ELEMENT_SUBNATL_RANK.S_PROTECTION_NEEDS,
ELEMENT_SUBNATL_RANK.S_MANAGEMENT_NEEDS,
ELEMENT_SUBNATL_RANK.S_EXEMP_SITE,
PLANT_CAS.S_HABITAT_COM,
to_char (ELEMENT_SUBNATL_RANK.VERSION_DATE, 'yyyy-mm-dd') esr_date,
ELEMENT_SUBNATL_RANK.VERSION_AUTHOR esr_author,
to_char (PLANT_CAS.VERSION_DATE, 'yyyy-mm-dd') pcas_date,
PLANT_CAS.VERSION_AUTHOR_NAME pcas_author,
element_global.g_rank,
element_global.element_global_id global_id,
element_global.elcode_bcd,
d_name_category.name_category_desc category

FROM 
element_global, 
scientific_name, 
d_name_category,
element_national,
element_subnational, 
element_subnatl_rank,
nation,
subnation,
d_eo_track_status,
taxon_subnatl_dist taxon_sd, 
d_origin est_origin, 
d_regularity est_regularity, 
d_dist_confidence est_confidence, 
d_curr_presence_absence est_presence,
plant_cas,
    d_number_eos, 
    d_number_good_eos,
    d_number_prot_eos,
    d_pop_size,
    d_range_extent,
    d_area_of_occupancy,
    d_lin_dist_of_occupancy,
    d_long_term_trend, 
    d_short_term_trend,
    d_intrin_vulnerability,
    d_enviro_specificity,
    d_threat,
    d_threat_immediacy,
    d_threat_scope,
    d_threat_severity

WHERE 
element_subnational.sname_id =  scientific_name.scientific_name_id
and element_global.element_global_id = element_national.element_global_id 
and element_national.element_national_id = element_subnational.element_national_id 
and element_national.nation_id = nation.nation_id
and element_subnational.subnation_id = subnation.subnation_id 
and element_subnational.d_eo_track_status_id = d_eo_track_status.d_eo_track_status_id (+) 
and scientific_name.d_name_category_id = d_name_category.d_name_category_id
and element_subnational.element_subnational_id = taxon_sd.element_subnational_id (+)
and element_subnational.element_subnational_id = element_subnatl_rank.element_subnational_id (+)
and element_subnational.element_subnational_id = plant_cas.element_subnational_id (+)
and taxon_sd.d_origin_id = est_origin.d_origin_id
and taxon_sd.d_regularity_id = est_regularity.d_regularity_id
and taxon_sd.d_dist_confidence_id = est_confidence.d_dist_confidence_id
and taxon_sd.d_curr_presence_absence_id = est_presence.d_curr_presence_absence_id
    and element_subnatl_rank.d_number_eos_id = d_number_eos.d_number_eos_id (+)
    and element_subnatl_rank.d_number_good_eos_id = d_number_good_eos.d_number_good_eos_id (+)
    and element_subnatl_rank.d_number_prot_eos_id = d_number_prot_eos.d_number_prot_eos_id (+)
    and element_subnatl_rank.d_pop_size_id = d_pop_size.d_pop_size_id (+)         
    and element_subnatl_rank.d_range_extent_id = d_range_extent.d_range_extent_id (+)
    and element_subnatl_rank.d_long_term_trend_id = d_long_term_trend.d_long_term_trend_id (+)
    and element_subnatl_rank.d_short_term_trend_id = d_short_term_trend.d_short_term_trend_id (+)
    and element_subnatl_rank.d_intrin_vulnerability_id = d_intrin_vulnerability.d_intrin_vulnerability_id (+)
    and element_subnatl_rank.d_enviro_specificity_id = d_enviro_specificity.d_enviro_specificity_id (+)
    and element_subnatl_rank.d_threat_scope_id = d_threat_scope.d_threat_scope_id (+)
    and element_subnatl_rank.d_threat_severity_id = d_threat_severity.d_threat_severity_id (+)
    and element_subnatl_rank.d_threat_immediacy_id = d_threat_immediacy.d_threat_immediacy_id (+)
    and element_subnatl_rank.d_threat_id = d_threat.d_threat_id (+)
    and nation.iso_nation_cd = 'US'    
and subnation.subnation_code = 'AZ'  /******CHANGE SUBNATION HERE *****/
and scientific_name.scientific_name in ('Castela emoryi')     /****CHANGE SNAME HERE****/

order by
scientific_name