Listagg is a function I seldom use, but it does come in handy sometimes.
It does have a strange quirk however, which I only recently discovered (and cannot explain):

-- Consider this simple query: select 'Q' || y as y2 from ( select 'Y' as y from dual where 0=1 ) x -- result: 0 rows -- This makes sense, because x returns 0 rows, therefore any "select y from x" also returns 0 rows. -- Now change the "select y from x" to "select listagg(y) from x": select 'Q' || listagg(y, ':') within group(order by y) as y2 from ( select 'Y' as y from dual where 0=1 ) x -- result: 1 row: 'Q' -- This is strange, because x still returns 0 rows, so why do we get a result ? -- And now add an extra "where 0=1" to the whole thing: select 'Q' || listagg(y, ':') within group(order by y) as y2 from ( select 'Y' as y from dual where 0=1 ) x where 0=1 -- result: 1 row: 'Q' -- Even this does not stop the listagg from somehow producing a row...