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')

Just copy your output and replace newlines with comma (or `','` depending on the datatype). — astentx, Jun 12 '21 at 20:10
PL/SQL Developer can do this - highlight the column in the results grid, right-click, "Copy as Expression List". Does Toad have something similar if you right-click on the results grid column? (I don't see anything in SQL Developer 20.2 but perhaps I am missing something.) — William Robertson, Jun 12 '21 at 21:57
@WilliamRobertson I haven't found an equivalent tool in Toad yet. — User1974, Jun 12 '21 at 23:06

score 1 · Answer 1 · answered Jun 12 '21 at 20:07

1

You can use a subquery in the where clause:

where id in (select id
             from . . .  -- your complicated query here
            )

answered Jun 12 '21 at 20:07

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Jun 12 '21 at 20:13

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

answered Jun 12 '21 at 20:13

astentx

6,393
2
16
25

User1974 · Answer 3 · 2022-01-18T04:45:09.133

Here is Maximo-specific version of @astentx's query:

with a as (

<<your query>>

)
select 'wonum in ('''
  || listagg(wonum, ''', ''') within group(order by wonum)
  || ''')' as where_clause
from a

And this query uses the syntax for Maximo's filtering fields:

with a as (

<<your query>>
    
    )
select '='
  || listagg(attributename, ', =') within group(order by attributename)
  || '' as where_clause
from a

Oracle: Generate WHERE clause from resultset ids?

3 Answers3

Linked