CODEWITHSUNDEEP
sqloracle-databaselistagg
erhan
erhan

Reputation: 15

Listagg result binding variable

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?

Upvotes: 0

Views: 404

Answers (1)

Popeye
Popeye

Reputation: 35900

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);

Upvotes: 1

Related Questions