The attached function provides a way to put together a list of multivalued fields for a row, delimited by a character, such as | or ,. 


You most likely already have the DELIMLIST function in your database, but if you don't, you can run the attached file while logged into a third-party tool, used to connect to Oracle (i.e. SQL Plus, SQL Developer, Tora, or Toad), as the biotics_dlink user.  Make sure to Grant/Refresh Database Privileges prior to trying to use the function.


To run the following queries in Query Builder, make sure not to include a ; at the end of the query, which is necessary when running the query in SQL Plus. Including the trailing ; in Query Builder will result in an error indicating Invalid Character. 


Examples:
SELECT
delimlist(
'SELECT d_terrestrial_habitat_id'
|| ' FROM animal_cag_terr_hab'
|| ' WHERE element_global_id = ' || element_global_id) AS terrestrial_habitat
FROM element_global WHERE element_global_id = 17442


In the following example, if you need single quotes somewhere inside your select statement (as in the DLOOKUP function), you have to type the single quote two times so it gets interpreted properly by DELIMLIST.


SELECT
elcode_bcd,
delimlist(
'SELECT DLOOKUP(''D_TERRESTRIAL_HABITAT'', d_terrestrial_habitat_id)'
|| ' FROM animal_cag_terr_hab'
|| ' WHERE element_global_id = ' || element_global_id) AS terrestrial_habitat
FROM element_global WHERE element_global_id = 17442