SQL help for retreiving Multilist31 from Page Three
Hi Friends,
We currently have a requirement to create reports using SQL results.
We have a peculiar scenario, To creata a column affected sites.
In Page Three Table for Multilist31 column. We have multiple values separated by commas.
Ex: ,3485202,3485207,
The above codes needs to refer LISTENTRY table to get the text data. Ex: Brussels;Peru.
Does anyone the SQL using STUFF function or XML function to decode the multiple values from MULTILIST31 column separated by commas.
Thank you,
Ram Mohan
Ex:
Hi Ram,
Following is the flow:
1. Fetch multilist value in an array.
2. Iterate through the array and append “;” or “,” after every entry till the loop ends.
3. Set the values to your variable and then insert it back in table.
Below is the sample code i used in my stored procedure.
————————————————————————————- Start of the body———————————————————————————–
TYPE t_Numbers IS VARRAY (10) OF agile.listentry.entryvalue%TYPE;
v_Numbers t_Numbers;
— Here v_id denotes the item id that you are fetching for your item in cursor.
begin
select p2.multilist31 into var from agile.page_three p2 where id=v_id;
len := LENGTH (var);
SELECT SUBSTR (var, 2, len – 2) INTO var5 FROM DUAL;
–DBMS_OUTPUT.put_line (‘aaaaaa’||var5);
if(var5 is not null) then
lv_sql :=’SELECT l.entryvalue FROM agile.listentry l WHERE l.entryid in(‘||var5||’) AND l.langid = 0′;
EXECUTE IMMEDIATE lv_sql BULK COLLECT INTO v_Numbers;
— DBMS_OUTPUT.put_line (‘Before For loop of ROHS’);
FOR i IN 1 .. v_Numbers.COUNT
LOOP
IF (i > 1)
THEN
v_multiList_final := v_multiList_final || ‘;’ || v_Numbers (i);
ELSE
v_multiList_final := v_Numbers (i);
END IF;
END LOOP;
else
v_multiList_final:=null;
end if;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_multiList_final := NULL;
END;
——————————————————————————-End of the body—————————————————————————————
Regards,
Arif
