Using a delimlist allows a query to return multiple values per record, concatenated into a single field. This is necessary in the case of one-to-many relationships which are seen as grids in Biotics 5. If you never need to return more than one value, it is unnecessary to use DELIMLIST. Following is an example of multiple counties for a single EO.

The hint to writing a successful delimlist is to first test the select within the delimlist, to make sure the query is written correctly and returning the expected results. In this example, 9675 is the example eo_id. Select a test eo_id that returns more than one value if possible. NOTE: in the following query, a and b are used as aliases for the table names eo_county and d_county, respectively, to minimize query length.

select b.county_name 

from eo_county a,d_county b 

where a.d_county_id = b.d_county_id and a.eo_id = 9675


Below, the query is incorporated into the delimlist, replacing the eo_id (9675) with '|| eo.eo_id, ', ') as "County Name". The delimlist query will be run for each record (EO, in this case) returned by the query in which the DELIMLIST is contained. Thus each eo_id queried is appended  (via || eo.eo_id, ) to the select statement and sent to the delimlist function which perfoms the query and returns the concatenated multi values with the separator ','.  Any character can be used as a delimiter. In this example, a comma and space act as the delimiter.

The as "County Name" is needed at the end to assign the column a name. If there is no space in the column name being given, quotes are not necessary. Nor is the as (in either case), but it makes the statement more readable/understandable. NOTE: A delimlist has to be incorporated into a query, it can not be run alone, so do not try to run the following:

DELIMLIST('select b.county_name from eo_county a,d_county b where a.d_county_id = b.d_county_id and a.eo_id = '|| eo.eo_id, ', ') as "County Name"

Following is a simple example of the DELIMLIST incorporated into the most basic query - notice how the eo_county and d_county tables are only queried in the delimlist portion, not the entire select statement in which it is contained. They are gathered by the delimlist function from the string in quotes 'select b.county_name from....' which is appended to the eo.eo_id queried in the overarching select statement using the sql concatenate symbol ||. NOTE: An additional where clause of EO.EO_ID<100 has been added to the query below in interest of speed!

select eo_id,
delimlist('SELECT distinct(D_COUNTY.COUNTY_NAME) FROM EO, EO_COUNTY, D_COUNTY WHERE EO_COUNTY.D_COUNTY_ID = D_COUNTY.D_COUNTY_ID (+) and EO_COUNTY.EO_ID='||eo.eo_id,', ') "County Name"
from eo

where EO.EO_ID<100


Getting more complex, the following DELIMLIST example concatenates data from more than one field (using ||" "|| to concatenate and add a space between the data values), as is helpful with Township, Range, and Section data. In this instance, a semicolon is used as the delimiter, whereas a comma was used in the example (County Name) above, just to demonstrate different symbols/characters can be used as the delimiter. NOTE: A delimlist has to be incorporated into a query, it can not be run alone, so do not try to run the following:

delimlist('SELECT distinct(EO_TRS.TOWN_RANGE||'' ''||EO_TRS.SECTION||'' ''||EO_TRS.TRS_NOTE) FROM EO, EO_TRS WHERE and (EO_TRS.DISPLAY_ORDER is null OR EO_TRS.DISPLAY_ORDER = 1) and EO_TRS.EO_ID='||eo.eo_id,'; ') TRS


Adding that to the example query above: NOTE: An additional where clause of EO.EO_ID<100 has been added to the query below in interest of speed!

select eo_id,
delimlist('SELECT distinct(D_COUNTY.COUNTY_NAME) FROM EO, EO_COUNTY, D_COUNTY WHERE EO_COUNTY.D_COUNTY_ID = D_COUNTY.D_COUNTY_ID (+) and EO_COUNTY.EO_ID='||eo.eo_id,', ') "County Name",

delimlist('SELECT distinct(EO_TRS.TOWN_RANGE||'' ''||EO_TRS.SECTION||'' ''||EO_TRS.TRS_NOTE) FROM EO, EO_TRS WHERE (EO_TRS.DISPLAY_ORDER is null OR EO_TRS.DISPLAY_ORDER = 1) and EO_TRS.EO_ID='||eo.eo_id,'; ') TRS
from eo

where EO.EO_ID<100



And to add one more step of complexity, the following DELIMLIST returns Survey Site Names for elements with EO Track Status of Y or W. Notice the tricky bit here is including the Y and W in the where clause within two single quotes. If only one single quote was used (as would typically be done in a select statement, the delimlist SELECT statement would be truncated prematurely. Hence, the use of two single quotes. NOTE: A delimlist has to be incorporated into a query, it can not be run alone, so do not try to run the following:

delimlist('SELECT distinct(ss.SURVEY_SITE_NAME) FROM EO, EO_SURVEY_SITE ss, D_EO_TRACK_STATUS, ELEMENT_SUBNATIONAL es WHERE EO.ELEMENT_SUBNATIONAL_ID = es.ELEMENT_SUBNATIONAL_ID and es.D_EO_TRACK_STATUS_ID = D_EO_TRACK_STATUS.D_EO_TRACK_STATUS_ID (+) and D_EO_TRACK_STATUS.EO_TRACK_STATUS_CD in (''Y'', ''W'') and ss.DISPLAY_ORDER = 1 and ss.EO_ID='||eo.eo_id,'; ') "Survey Site"


Adding that to the example query above, we get: NOTE: An additional where clause of EO.EO_ID<100 has been added to the query below in interest of speed!

select eo_id,
delimlist('SELECT distinct(D_COUNTY.COUNTY_NAME) FROM EO, EO_COUNTY, D_COUNTY WHERE EO_COUNTY.D_COUNTY_ID = D_COUNTY.D_COUNTY_ID (+) and EO_COUNTY.EO_ID='||eo.eo_id,', ') "County Name",

delimlist('SELECT distinct(EO_TRS.TOWN_RANGE||'' ''||EO_TRS.SECTION||'' ''||EO_TRS.TRS_NOTE) FROM EO, EO_TRS WHERE EO.EO_ID<100 and (EO_TRS.DISPLAY_ORDER is null OR EO_TRS.DISPLAY_ORDER = 1) and EO_TRS.EO_ID='||eo.eo_id,'; ') TRS,

delimlist('SELECT distinct(ss.SURVEY_SITE_NAME) FROM EO, EO_SURVEY_SITE ss, D_EO_TRACK_STATUS, ELEMENT_SUBNATIONAL es WHERE EO.EO_ID<100 and EO.ELEMENT_SUBNATIONAL_ID = es.ELEMENT_SUBNATIONAL_ID and es.D_EO_TRACK_STATUS_ID = D_EO_TRACK_STATUS.D_EO_TRACK_STATUS_ID (+) and D_EO_TRACK_STATUS.EO_TRACK_STATUS_CD in (''Y'', ''W'') and ss.DISPLAY_ORDER = 1 and ss.EO_ID='||eo.eo_id,', ') "Survey Site"
from eo

where EO.EO_ID<100


And for a completely different example, the SQL below is used to create an Oracle view, which will just let you query the view in the future (i.e. select * from wy_data_request) rather than running the long select statement each time. The substr function is used to truncate all columns longer than 255, which is only pertinent if the data is being used in a shapefile. Geodatabases do not have such restrictions. Also column names are kept 10 characters or less due to a shapefile's attribute table limitation (dBase IV format).

To create an Oracle view, run the following syntax while logged in to your favorite third-party tool (i.e. SQLPlus, SQL Developer, Tora, Toad...) as biotics_dlink.  After creating the view, make sure to Grant/Refresh Database Privileges.

 CREATE OR REPLACE VIEW wy_data_request
AS
select EO.shape_id,
EO.eo_id,
substr(element_subnational.s_primary_common_name,1,255) scomnamedb,
scientific_name.scientific_name snamedb,
element_subnational.s_rank,
d_eo_track_status.eo_track_status_cd trackstat,
eo.data_sensitive_eo_ind datasenseo,
eo.last_obs_date last_obs,
eo.first_obs_date first_obs,
substr(eo.general_com,1,255) gen_desc,
substr(eo.directions,1,255) directon,
eo.eo_type_bcd eo_type,
element_global.g_rank,
substr(DELIMLIST('select b.county_name from eo_county a,d_county b where a.d_county_id
= b.d_county_id and a.eo_id = '|| eo.eo_id, '|'),1,255) as county,
substr(DELIMLIST('select b.mapsheet_cd from eo_mapsheet a,d_mapsheet b where a.d_mapsheet_id
= b.d_mapsheet_id and a.eo_id = '|| eo.eo_id, '|'),1,255) as mapsheet,
substr(DELIMLIST('select a.managed_area_name from ma a,eo_managed_area b
where a.managed_area_id = b.managed_area_id and
b.eo_id = '|| eo.eo_id, ' | '),1,255) as ma_name,
d_usesa.usesa_cd
FROM
EO,
SHAPE,
SCIENTIFIC_NAME,
element_subnational,
d_eo_track_status,
element_national,
element_global,
taxon_global,
d_usesa
where shape.Deleted_ind = 'N'
and EO.Shape_ID = Shape.Shape_ID
and EO.element_subnational_id = element_subnational.element_subnational_id
and element_subnational.element_national_id = element_national.element_national_id
and element_national.element_global_id = element_global.element_global_id
and element_global.element_global_id = taxon_global.element_global_id (+)
and taxon_global.d_usesa_id = d_usesa.d_usesa_id (+)
and element_subnational.sname_id = scientific_name.scientific_name_id
and element_subnational.d_eo_track_status_id = d_eo_track_status.d_eo_track_status_id (+);