ELEMENT_SUBNATIONAL.ELCODE_BCD field was removed from the date model on December 21, 2016. While we are aware that many programs referred to this field, there are numerous reasons why we have chosen to remove it, a few of which are outlined below.

  • ELEMENT_SUBNATIONAL.ELCODE_BCD was "retired" during the Biotics 4 to Biotics 5 conversion and has not been populated or maintained by NatureServe since.
  • ELCODE_BCD also exists in ELEMENT_GLOBAL and having duplicate fields is bad practice in a relational database model and creates the possibility for inconsistent data in the two fields, which can cause problems.
  • There isn't a field for ELCODE_BCD in the ELEMENT_SUBNATIONAL interface, so there is no way to tell what the value is other than by querying it.
  • Given that the field has not been auto-populated since Biotics 4, utilizing ELEMENT_SUBNATIONAL.ELCODE_BCD in views and queries may result in the exclusion of records from query results as the value may be null for newly created records, unless explicitly populated.

To assist programs with this transition, we are providing you with a query that will help identify references to ELEMENT_SUBNATIONAL.ELCODE_BCD prior to the removal of it from the data model. The instructions follow:

  1. Run the following query in Query Builder (required due to privileges) and output the results to HTML.
    • select view_name, text from all_views where owner in ('BIOTICS_USER') and view_name in (select name from all_dependencies WHERE type = 'VIEW' AND referenced_owner = 'BIOTICS_USER' AND referenced_name = 'ELEMENT_SUBNATIONAL')
  2. Search (CTRL+F) the HTML results for ELEMENT_SUBNATIONAL.ELCODE_BCD
  3. Make note of the view names that refer to ELEMENT_SUBNATIONAL.ELCODE_BCD or copy/paste the actual SQL provided into notepad so you can refer to it as you update your views going forward.
  4. After fixing any affected view(s), Grant/Refresh Database Privileges or your changes will not be seen.


See the Searching views which reference a specific field/column name Solution.


To update a view to point to ELCODE_BCD in the ELEMENT_GLOBAL table (rather than the ELEMENT_SUBNATIONAL table), use the EST_ELCODE function which should exist in your database. See the Function to retrieve ELCODE_BCD for an EO solution for instructions.


BX-1614