0

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;
ID Name
1 Alex
2 Amy
3 Jim 
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:

ID Name
1 Alex
2 Amy

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

Improve this question

1 Answer 1

Reset to default
1

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>
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You're welcome. Though, I'm afraid you're mistaken - LISTAGG does not append trailing separator (comma in this case); XMLAGG does.
Hm, that's strange. See edited answer which demonstrates that there's no trailing comma. Which Oracle database version do you use? Does ID (by any chance) contain comma, i.e. column's contents is "717033,"? If not, could you post some more sample data?
Right. OK then, I'm glad you found the culprit and simplified your query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.