Hi Nick,

You can create custom function like this one to retrieve the values for a multilist

CREATE OR REPLACE
FUNCTION “GET_MULTILIST_COUNTRIES”
( csv_string IN VARCHAR2
) RETURN VARCHAR2 AS
TYPE ListCurTyp IS REF CURSOR;
resultString VARCHAR2(2500);
sql_string VARCHAR2(2500);
csv_values VARCHAR2(2500);
list_cursor ListCurTyp;
list_value VARCHAR2(2500);
BEGIN
DBMS_OUTPUT.ENABLE(200000);
dbms_output.put_line(‘list value = ‘);
IF csv_string IS NOT NULL THEN
csv_values := LTRIM(csv_string,’,’);
csv_values := RTRIM(csv_values,’,’);
IF INSTR(csv_values,’,’)=0 THEN –only one value in csv string
SELECT LISTENTRY.ENTRYVALUE into resultstring FROM LISTENTRY
WHERE PARENTID = 365 AND LISTENTRY.ENTRYID = csv_values AND ROWNUM = 1;
resultString := substr(resultString,length(resultString)-2,2);
RETURN resultString;
ELSE –multiple csv values
sql_string := ‘select LISTENTRY.ENTRYVALUE from LISTENTRY
WHERE PARENTID = 365 AND ENTRYID in (SELECT TO_NUMBER(xt.column_value)
FROM XMLTABLE(”’||csv_values||”’) xt
)’;

OPEN list_cursor FOR sql_string;
LOOP
FETCH list_cursor INTO list_value;

EXIT WHEN list_cursor%NOTFOUND;
list_value := substr(list_value,length(list_value)-2,2); –extract the country code
IF LENGTH(resultstring)>0 THEN
resultstring := resultstring ||’, ‘|| list_value;
ELSE
resultstring := list_value;
END IF;
END LOOP;
CLOSE list_cursor;
RETURN resultstring;
END IF;
ELSE
RETURN NULL;
END IF;
END GET_MULTILIST_COUNTRIES;

You can get the exact value of a particular list, specifying in the inner query, another condition on PARENTID = XXX where XXX is the id of the list that you are looking for

Wrested with the same issue myself, I believe the only way would be to use pl/sql which have yet to explore how to code it. So I would too appreciate a model answer.

Conceptually I think you your product_lines result could be turned into an array, then you could iterate over the array querying the listentry table and substituting the list item text into the array.

create functions and use in scripts,

Agreed, a function is the only way to go in a query. Note that in general you will need to use the correct parent ID for the list you will be processing, and as such I would pass it in as an argument to the function. For most multi-lists, this will work just fine. But you also have to take into account when the attribute has too many ID values assigned to hold in the actual database attribute (they usually have a max length of 765, and so can hold somewhere around 100 values), and so the values are then stored in MSATT. Or when the string of values that is created from the list of ID values exceeds the max length for VARCHAR2 data. But in most cases, those things are not an issue.