I hava a this query select LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) as "id_no" from student_all
.
It is working but I want to use result in a variable so I wrote this query like this
select LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) into a_value from student_all
but it is not working. My error is missing keyword.
Also I wanted to write a function for the testing but same error.I did not created function.
CREATE OR REPLACE FUNCTION list_id( periods IN NUMBER, p_student_id IN NUMBER) RETURN VARCHAR2 AS str VARCHAR2(200); BEGIN SELECT LISTAGG(id, ',') WITHIN GROUP ( ORDER BY id DESC) INTO str FROM student_all WHERE status = 'IN' AND id = p_student_id FETCH FIRST periods ROWS ONLY RETURN str; END;
Do you have any idea?
Advertisement
Answer
There are multiple issues with your code.
- Semicolon is missing at the end of the
SELECT
statement FETCH
clause is not used properly.
I think you are looking for the code to fetch only a few rows and then do LISTAGG
. You can use the following code.
SELECT LISTAGG(id, ',') WITHIN GROUP (ORDER BY id DESC) INTO str FROM (SELECT ID FROM student_all WHERE status = 'IN' AND id = p_student_id ORDER BY ID FETCH FIRST periods ROWS ONLY);