The EST_USESA function:

-- Returns the USESA status of an EST, when you pass in an element_subnational_id.
-- Returns the first non-null value from these fields in this order:
-- taxon_subnational.state_interpreted_usesa
-- taxon_global.interpreted_usesa
-- d_usesa.usesa_cd
-- If it encounters "No Status" at any level, it will stop there and return .
-- Tyler Christensen, 18 Feb 2004

Run the attached function while logged into SQLPlus as the biotics_dlink user.

Once the function has been created, it can be used in a query. An example follows from Lynn Kutner of how to use the EST_USESA function in a query. This function will use the new USESA fields to calculate whether an element has US federal status in your state.

---------------------------------------------------
SAMPLE SQL for ELEMENT_SUBNATIONAL records with USESA status;
includes the est_usesa function
includes all element_global and element_subnational USESA fields
AUTHOR: Lynn Kutner, NatureServe
DATE: August 24, 2004

SELECT est.element_subnational_id,
egt.elcode_bcd,
nation.iso_nation_cd nation,
subnation.subnation_code subnation,
gname.scientific_name gname, egt.g_primary_common_name gcomname,
sname.scientific_name sname, est.s_primary_common_name scomname,
d_usesa.usesa_cd,
substr(taxon_global.usesa_date,1,10) usesa_date,
taxon_global.interpreted_usesa global_interpreted_usesa,
taxon_global.usesa_com,
taxon_subnational.state_interpreted_usesa,
substr(taxon_subnational.state_interpreted_usesa_date,1,10) s_interp_usesa_date,
est_usesa(est.element_subnational_id) est_usesa_function,
taxon_subnational.state_usesa_com

FROM
element_subnational est,
element_national ent,
element_global egt,
subnation,
nation,
d_usesa,
taxon_global,
taxon_subnational,
scientific_name gname,
scientific_name sname

WHERE
est.subnation_id = subnation.subnation_id
and est.element_national_id = ent.element_national_id
and ent.element_global_id = egt.element_global_id
and ent.nation_id = nation.nation_id
and egt.element_global_id = taxon_global.element_global_id
and est.element_subnational_id = taxon_subnational.element_subnational_id (+)
and egt.gname_id = gname.scientific_name_id
and est.sname_id = sname.scientific_name_id
and taxon_global.d_usesa_id = d_usesa.d_usesa_id (+)

/* THE FOLLOWING RETURNS ALL RECORDS FOR A SUBNATION WITH A USESA VALUE */

and (taxon_global.interpreted_usesa is not null or taxon_global.d_usesa_id is not null or
taxon_subnational.state_interpreted_usesa is not null)