I created a select where it removes a repeated word from a string. The problem is that an error message appears when I run this select on oracle11, saying "ORA-00907: missing right parenthesis". This same select runs smoothly in oracle12. Does anyone know how to solve?
[with str as (
select 'Example1 ; Example2 ; Example3 ; Example2 ; Example1' s
from dual
), rws as (
select distinct trim(regexp_substr(s, '[^ ; ]+', 1, r.rn)) s
from str, lateral (
select level rn from dual
connect by level <= length (regexp_replace(s, '[^ ; ]+')) + 1
) r
)
select listagg(s, ' ; ') within group (order by s) dist
from rws;]