/* Delimlist for element_subnational distribution fields (taxon_subnatl_dist ); values are concatenated to keep each "row"
of the distribution grid together; includes a case statement to return the d_population data only where relevant (animals)
AUTHOR: Lynn Kutner, NatureServe
DATE: August 26, 2004 */

SELECT
est.element_subnational_id,
d_name_category.display_value name_category,
sname.scientific_name s_name,
est.s_primary_common_name,
est.s_rank,
DelimList('SELECT ''(Origin: '' || d_origin.origin_desc ||
''; Regularity: '' || d_reg.regularity_desc ||
''; Confidence: '' || d_conf.display_value ||
''; Current Presence: '' || d_pres.curr_presence_absence_desc || (case when s_dist.d_population_id is not null then
''; Population (animals): '' || d_pop.population_desc else null end) || '')''
FROM taxon_subnatl_dist s_dist, d_curr_presence_absence d_pres,
d_dist_confidence d_conf, d_origin, d_population d_pop, d_regularity d_reg ' || '
WHERE s_dist.d_curr_presence_absence_id = d_pres.d_curr_presence_absence_id (+)
and s_dist.d_dist_confidence_id = d_conf.d_dist_confidence_id (+)
and s_dist.d_origin_id = d_origin.d_origin_id (+)
and s_dist.d_population_id = d_pop.d_population_id (+)
and s_dist.d_regularity_id = d_reg.d_regularity_id (+)
and s_dist.element_subnational_id = ' || est.element_subnational_id, '; ') AS subnational_distribution

FROM element_subnational est,
scientific_name sname,
d_name_category

WHERE est.sname_id = sname.scientific_name_id
and sname.d_name_category_id = d_name_category.d_name_category_id
and d_name_category.name_type_cd in ('P', 'A')