We are needing some assistance/suggestions in rewriting the following Oracle query. Query is basically creating a list(listagg) of a list of regions gathered from abc.table123 (security config table, each row is different region), and then needs to be compared to a column called regionlist in def.table456. Regexplike is giving us some issues, and believe tochar is as well. Any help would be great, thanks!
WHERE REGEXP_LIKE (
|| (SELECT SUBSTR (
WHEN id_value = 'AM' THEN 'AMERICAS'
WHEN id_value = 'EU' THEN 'EUROPE'
WHEN id_value = 'AS' THEN 'ASIA'
WHEN id_value = 'JP' THEN 'JAPAN'
WHEN id_value = 'XX' THEN NULL
WHEN id_value IS NULL THEN NULL
WITHIN GROUP (ORDER BY id_value)),
WHERE id_type = 'REGION' AND userid = 'user_name')