The Query Builder tool allows you to create and execute ad-hoc SQL Select statements against the Biotics database. More specific details on how to develop queries (e.g., querying numbers, combining expressions, using nulls), are provided in the Build a Query topic. Some of the main features include:
View tables, views, and columns
Export the result set data to a file (CSV, Excel, Shapefile, Geodatabase, Working List) or in another browser window (Html)
NOTE: To cancel a long-running query, close the Query Builder tab. As of version 5.9.2, doing so will kill the query session in Oracle. This will not work when Query Output of Shapefile or Geodatabase has been chosen as they utilize ogr2ogr rather than Oracle itself.
Queries run in the Query Builder can theoretically select an unlimited number of records but is limited by file size, to prevent the resulting file from exceeding the space available on the server and ultimately killing the entire application.
From the Queries and Reports menu, select Query Builder. Or, from the Biotics Main Menu, click Query Builder.
Within the Query Builder, all Tables and Views are listed in the left section. Expand a Table/View to see the columns therein. Use the icon to contract an expanded section.
Upon finishing the query, select the desired Query Output and click the Run Query.
CSV - exports to a comma delimited file which can be opened in various applications such as MS Excel, Access... The results can be opened immediately or saved to a file.
Excel - exports a Microsoft Excel spreadsheet. The results can be opened immediately or saved to a file.
Html - opens the results in a new browser tab.
Shapefile or Geodatabase- as long as the query contains a SHAPE column (DATA_TYPE='SDO_GEOMETRY'), the results will be exported to a shapefile. This functionality is limited to Users granted the Export Shapefile privilege within the Biotics - Mapper Security Function, typically assigned to the Mappers User Group.
NOTE: Query Builder uses ogr2ogr to generate shapefiles and geodatabases which has two limitations:
Neither comment nor blank lines prior to the select statement within the sql query can be parsed, so do not include them at the beginning of the query. Including them will result in an empty shapefile/geodatabase. Commented lines within the statement must use /* and */ to fully enclose them rather than double hyphens (--).;
CLOB fields (EO_DATA, GEN_DESC,...) need to be converted to string fields within the select statement when exporting to either shapefile or geodatabase. Within a shapefile, the value will be truncated to 255 characters due to the limitation of the shapefile's attribute table, which utilizes a dbfIV file. In contrast, geodatabases do not have this limitation, however within Oracle, string fields are limited to 4000 characters, so limit the data length to a maximum of 4000 characters when exporting to geodatabase.
For example, replace EO_DATA in the query with dbms_lob.substr(EO.EO_DATA,255) as EO_DATA to convert the data to string format.
Utilized within a select statement: select eo_shape.shape, eo_shape.eo_id, dbms_lob.substr(EO.EO_DATA,255) as EO_DATA from eo_shape, eo where eo_shape.eo_id=eo.eo_id
The following query utilizes the SHAPE column from the EO_SHAPE table (using the application's native projection [i.e. UTM Zone 18N - NAD83]) in combination with the data from the EO_VIEW_ALL_ATT_MAP view: select eo_shape.shape, eo_view_all_att_map.* from eo_shape, eo_view_all_att_map where eo_shape.eo_id=eo_view_all_att_map.eo_id
Tables which include SHAPE in the native projection include:
Working List - the Primary Key (i.e. ELEMENT_SUBNATIONAL_ID, SOURCE_FEATURE_ID,...), for the desired Working List Type, must be included as a column in the query.
Element: element_global_id, element_national_id, or element_subnational_id
NOTE: If a query contains more than one of the element lD fields within the query results, the working list will be saved according to the first ID listed (i.e. element_global_id)
Element Group: element_group_id
Element Occurrences: eo_id
Source Features: source_feature_id
Managed Areas: managed_area_id
Conservation Sites: conservation_site_id
Scientific Names: scientific_name_id
References: reference_id
Failure to include the appropriate ID column (Primary Key) for the given Working List Type will result in the following error: Error: The results returned from the query does not contain a valid ID column for creating this type of working list
NOTE: Because the user is prompted to save the query results to a Working List prior to the query being run, it is recommended that you verify the query is successful by outputting to Html first. Once the query has been verified to run successfully, change the Query Output to Working List and Run Query again.
Within the Save Working List dialog, a new list will be created by default (Save as New List). Alternatively, to update an existing list, select the desired list from amongst those listed in the Filtered Working Lists section. Note the automatic change to Update Selected List rather than Save as New List.
Working List Type - select the type of records to be saved to the working list
Working List Group - (Optional) filters the working lists displayed according to the selection within the dropdown menu.
Only show access control lists - check to narrow the working lists displayed to those limited to specific Users/User Groups
Only show my lists - uncheck in order to see the shared lists created by other users.
Working List Name Contains - enter a value to find a specific working list and click Search.
Within the Working List Details section, enter (Create New List) or edit (Update Selected List) the following information:
Working List Name: (REQUIRED) Names may be up to 50 characters long and must be unique for a given user.
Working List Description: (Optional) description up to 500 characters in length.
Shared: from the dropdown menu, indicate Shared status. The default is Not Shared. NOTE: Any working list which will be an Access Control List must be Shared - No Updates Allowed.
Working List Group: (Optional) indicate the type of data stored within the working list
Access Control List: (limited by permission granted to User Group) - Indicates the working list will be restricted to Users/User Groups granted Restricted Access privileges for the specific working list. NOTE: Any working list which will be an Access Control List must be Shared - No Updates Allowed. NOTE: Access Control List cannot be modified. If an existing working list should be an Access Control List, then Copy a Working List and upon creating the new list, indicate that it is an Access Control List. Don't forget to delete the original working list!
Click OK to save the working list.
The query results are displayed below the Query Output section. Once the query is complete, follow the instructions in the Status section of the query results table. NOTE: Results must be downloaded within 30 minutes of when the query finishes because older results are periodically deleted to free up disk space on the server. If your results are no longer available, rerun the query. NOTE: To cancel a long-running query, close the Query Builder tab. As of version 5.9.2, doing so will kill the query session in Oracle. NOTE: The following error results when the query exceeds 32,767 characters and the selected Query Output is Excel due to a limitation in Excel for cell length. The workaround is to choose Csv or another Query Output. Error: The maximum length of cell contents (text) is 32,767 characters
Click the Clear Query Results button to clear the query results. This may be desirable after a number of queries have been run.
a Table/View to see the columns therein. Use the 
icon to contract an expanded section.
.
to narrow the working lists displayed to those limited to specific Users/User Groups
in order to see the shared lists created by other users.
to clear the query results. This may be desirable after a number of queries have been run.