user1251973 user1251973 - 1 month ago 16
SQL Question

ORA - 02287 sequence number not allowed here

I have a table name test which has three columns id, m_id and s_m_id

I am executing below query

select id,test.nextval listagg(m_id || ',' || s_m_id, ';') within group (order by m_id) as merge_ids
from test t group by id


than I am getting error ORA - 02287 sequence number not allowed here.

Answer

You're trying to do too many things in one go. Create a subquery for the grouping and add the sequence numbers later:

select id, test.nextval, merge_ids
from (
    select id, listagg(m_id || ',' || s_m_id, ';') within group (order by m_id) as merge_ids
    from test t
    group by id
)