I know I've seen this before, but can't come up with the search terms to find it.

I have a CTE returning a comma separated list from the below table:

Table

Create table Table
(
    ID Number
    , Name varchar2(100)
);

insert all
    into Table (ID, Name) values (1, 'Alex')
    into Table (ID, Name) values (2, 'Amy')
    into Table (ID, Name) values (3, 'Jim')
select * from dual;

select substr(
        listagg(Table.ID || ',') within group (order by null)
        , 1
        , length(listagg(Table.ID || ',') within group (order by null)) - 1
    ) IDs
from Table
where Name like 'A%'

Which gives me the results: 1,2

I'm trying to use this result in a query's in clause:

with CTE as
(
    select substr(
            listagg(tbl.ID || ',') within group (order by null)
            , 1
            , length(listagg(tbl.ID || ',') within group (order by null)) - 1
        ) IDs
    from Table
    where Name like 'A%'
)
select *
from Table
where cast(ID as varhcar2(1000)) in (select IDs from CTE) --Use results here
--believe the cast is required to compare, otherwise get a ORA-01722: invalid number

Which I want to return:

How can I use the CTE's resulting IDs string as the parameter of my in clause?

CodePudding user response：

I'm afraid I don't understand your "problem". CTE is really strange; SUBSTR of something? Why? LISTAGG returns the same result anyway. Then you want to ... what? split that result so that you could use it in another query? As if you want to make it as complex as possible (and beyond) to solve something "simple". Therefore: what real problem are you trying to solve?

Anyway, here you go: you'll have to split aggregated string into rows if you want to use it in IN clause:

SQL> with CTE as
  2    (select listagg(ID || ',') within group (order by null) IDs
  3     from Test
  4     where Name like 'A%'
  5    )
  6  select *
  7  from Test
  8  where id in (select regexp_substr(IDs, '[^,] ', 1, level)
  9               from CTE
 10               connect by level <= regexp_count(IDS, ',')   1
 11              );

        ID NAME
---------- ----------
         1 Alex
         2 Amy

SQL>

The same result is returned by a simple

SQL> select *
  2  from Test
  3  where Name like 'A%';

        ID NAME
---------- ----------
         1 Alex
         2 Amy

SQL>

That's why I asked: what problem are you trying to solve?

[EDIT] As of trailing comma: there's none, at least not any Oracle version I used (11g, 12c, 18cXE, 21cXE):

SQL> select listagg(id, ',') within group (order by null) result from test;

RESULT
------------------------------
1,2,3

SQL> select listagg(name, ',') within group (order by null) result from test;

RESULT
------------------------------
Alex,Amy,Jim

SQL>