| REFERENCE_ID |
the scientific_name_id |
| USED_AS |
the name of the column where the reference_id is used. Most of the time this will be reference_id, but there exceptions. |
| TABLE_USED |
the table in which the reference_id is used |
| RECORD_ID |
the primary key for the record in which the reference_id is used |
| ASSOC_ID_DESC |
name of the associated id field |
| ASSOC_ID |
the id of the associated record (element_global_id, element_subnational_id, eo_id, scientific_name_id, conservation_site_id, etc.) |
| ASSOC_NAME* |
name of associated record, e.g. gname, sname, etc. |
| D_MAINTAINED_BY_STATUS_ID |
d_maintained_by_status_id of associated record, if applicable |
| INACTIVE_IND |
inactive status of associated record, if applicable |
*ASSOC_NAME is not always unique to the record, e.g. for EOs it's the sname, for COMM_CAL it's the gname, for ELEMENT_MANAGED_AREA it's the MA Name.
Here are some examples of how to use it:
1. If you just want to know where a particular reference is used, it's this simple; just enter the ID for the reference record you're interested in for "reference_id":
select * from reference_usage_vw where reference_id = 100077
2. Here's a way to get a list of each reference_id, each table that it's found in, and how many occurrences there are in that table of that id:
select reference_id, table_used, count(distinct assoc_id)
from reference_usage_vw
group by reference_id, table_used
order by reference_id, table_used