Oracle: Generate WHERE clause from resultset ids?

Question

In Toad for Oracle 12:

I've selected rows from a table using a complex query.

I want to select those same rows in the system's application's WHERE clause.

However, the application doesn't support full SELECT statements, only WHERE clauses. And occasionally, it doesn't allow complex queries as subqueries in the WHERE clause, which is the case for my current query.

---

As an alternative, is there a way to get Toad to generate a WHERE clause from the resultset's IDs?

I would copy/paste the WHERE clause into the application. This is a common task, so it would be nice if there was something easy like a button in Toad that would do this.

---

Example:

Use the resultset...

ID    VAL
 1      A
 2      B
 3      C

...to generate a WHERE clause:

where id in (1,2,3)

Or if the IDs are text:

where id in ('1','2','3')

Answer 1

You can apply listagg function to your output and concatenate output IDs to list:

with a as (
  <your_current_query>
)
select 'where id in ('
  || listagg(id, ',') within group(order by id)
  || ')' as where_clause
from a