/* Returns separation distance for suitable habitat & unsuitable habitat distances*/ /* Data from both a direct EGT-eo_specs link and a EGT-Element_Group-Eo_specs link.*/ /* Note: Though it is not common, when an EO_Spec exists for an individual species and for a species group in which the species is a member, query will return only the row(s) from the individual species relationship, as done in Biotics. */ /* Query will sometimes return duplicates (on EGT_id) when there are EO_Specs for more than 1 location use class (e.g. breeding, nonbreeding, migratory stopover). The EST ID & LUC have been concatenated in the EST_BY_LUC column, to enable joining according to this information*/ /* The SEP_DISTANCE column is populated with Suitable Habitat Separation Distance but should be updated to reflect custom separation distances, where appropriate. The data from this column is used within the Observations.tbx */ /* Query for appropriate subnation_id: SELECT * FROM SUBNATION WHERE SUBNATION_NAME='Texas' /* CHANGE subnation_id=41 to appropriate subnation_id in two places! CTRL+F to find them*/ SELECT est.element_subnational_id, egt.elcode_bcd, luc.location_use_class_desc, animal_eo_specs.sep_dist_suitable_habitat||plant_eo_specs.sep_dist_suitable_habitat as suitable_hab_sep_distance, animal_eo_specs.sep_dist_unsuitable_habitat||plant_eo_specs.sep_dist_unsuitable_habitat as unsuitable_hab_sep_distance, animal_eo_specs.sep_dist_suitable_habitat||plant_eo_specs.sep_dist_suitable_habitat as sep_distance, est.element_subnational_id||' - '||luc.location_use_class_desc est_by_LUC FROM ( select egt.element_global_id, (case when eo_specs.eo_specs_id is not null then eo_specs.eo_specs_id else eo_specs2.eo_specs_id end) eospecs_id, egp.element_group_id from element_national ent, element_subnational est, element_global egt, eo_specs, element_group egp, element_group_membership egm, eo_specs eo_specs2 where est.subnation_id = 41 and egt.element_global_id=ent.element_global_id and ent.element_national_id=est.element_national_id and egt.element_global_id = eo_specs.element_global_id (+) and egt.element_global_id = egm.element_global_id (+) and egm.element_group_id = egp.element_group_id (+) and egp.element_group_id = eo_specs2.element_group_id (+) ) egt_eospecs, element_global egt, eo_specs, eo_specs_detail esd, d_location_use_class luc, animal_eo_specs, plant_eo_specs, community_eo_specs, element_national ent, element_subnational est WHERE egt.element_global_id = egt_eospecs.element_global_id and egt_eospecs.eospecs_id = eo_specs.eo_specs_id and eo_specs.eo_specs_id = esd.eo_specs_id and esd.d_location_use_class_id = luc.d_location_use_class_id (+) and esd.eo_specs_detail_id = animal_eo_specs.eo_specs_detail_id (+) and esd.eo_specs_detail_id = plant_eo_specs.eo_specs_detail_id (+) and esd.eo_specs_detail_id = community_eo_specs.eo_specs_detail_id (+) and egt.element_global_id=ent.element_global_id and ent.element_national_id=est.element_national_id and est.subnation_id = 41 ORDER BY est.element_subnational_id