Runs off a working list and reports global habitat information for animals. Could be modified for use with plant elements and subnational data by substituting analogous tables and fields.

MODIFY 1 WORKING LIST NUMBER BELOW
 
SELECT
eg.element_global_id
, eg.elcode_bcd
, sn.scientific_name
, eg.g_primary_common_name
, eg.g_rank

,
DelimList('SELECT dph.palustrine_habitat_desc'
|| ' FROM animal_cag_palus_hab acph, d_palustrine_habitat dph '
|| ' WHERE acph.d_palustrine_habitat_id = dph.d_palustrine_habitat_id and acph.element_global_id = '||eg.element_global_id, ', ')

AS PALUSTRINE_HABITAT
,
DelimList('SELECT dlh.lacustrine_habitat_desc'
|| ' FROM animal_cag_lacus_hab aclh, d_lacustrine_habitat dlh '
|| ' WHERE aclh.d_lacustrine_habitat_id = dlh.d_lacustrine_habitat_id and aclh.element_global_id = '||eg.element_global_id, ', ')

AS LACUSTRINE_HABITAT
,
DelimList('SELECT drh.riverine_habitat_desc'
|| ' FROM animal_cag_riverine_hab acrh, d_riverine_habitat drh '
|| ' WHERE acrh.d_riverine_habitat_id = drh.d_riverine_habitat_id and acrh.element_global_id = '||eg.element_global_id, ', ')

AS RIVERINE_HABITAT
,
DelimList('SELECT dsubh.subterranean_habitat_desc'
|| ' FROM animal_cag_subterr_hab acsubh, d_subterranean_habitat dsubh '
|| ' WHERE acsubh.d_subterranean_habitat_id = dsubh.d_subterranean_habitat_id and acsubh.element_global_id = '||eg.element_global_id, ', ')

AS SUBTERRANEAN_HABITAT
,
DelimList('SELECT dmh.marine_habitat_desc'
|| ' FROM animal_cag_marine_hab acmh, d_marine_habitat dmh '
|| ' WHERE acmh.d_marine_habitat_id = dmh.d_marine_habitat_id and acmh.element_global_id = '||eg.element_global_id, ', ')

AS MARINE_HABITAT
,
DelimList('SELECT dspech.special_hab_factor_desc'
|| ' FROM animal_cag_spec_factor acspech, d_special_hab_factor dspech '
|| ' WHERE acspech.d_special_hab_factor_id = dspech.d_special_hab_factor_id and acspech.element_global_id = '||eg.element_global_id, ', ')

AS SPECIAL_HAB_FACTOR
,
cag.g_habitat_com AS HABITAT_COMMENTS

FROM
element_global eg
, scientific_name sn
, working_list_element_data wled
, animal_cag cag

WHERE
wled.working_list_id = 101245 /* MODIFY WORKING LIST NUMBER */
and wled.data_id = eg.element_global_id
and eg.gname_id = sn.scientific_name_id
and eg.element_global_id = cag.element_global_id

ORDER BY
eg.elcode_bcd