CODEWITHSUNDEEP
sqloracle-databasestring-aggregation
krishnakant
krishnakant

Reputation: 375

Unable to use LISTAGG output in a subquery. ORACLE

I am getting the comma separated value from the below query
SELECT LISTAGG('''' || student_name || '''',',') WITHIN GROUP (ORDER BY student_name) FROM students;

However, when I put in subquery it doesn't give any result.
select * from students where student_name in ('A', 'B'); --give two rows

select * from students where student_name in (SELECT LISTAGG('''' || student_name || '''',',') WITHIN GROUP (ORDER BY student_name) FROM students;) -- no rows

Upvotes: 0

Views: 737

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You don't understand how in works. It does not work with strings. It works with multiple items.

So, the right way to write the logic is:

select  *
from students
where student_name in (select student_name from students) -- no rows;

Your query is essentially doing:

where student_name in ('aaron,beth,calvin,debbie,...')

The in list has one item, so this is equivalent to:

where student_name = 'aaron,beth,calvin,debbie,...'

And no student name (if you have more than one row) can be the concatenated names of all the students.

Upvotes: 2

Related Questions