CODEWITHSUNDEEP
sqloracle-databasewhere-clausetoadmaximo
User1974
User1974

Reputation: 386

Oracle: Generate WHERE clause from resultset ids?

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

Upvotes: 0

Views: 482

Answers (3)

User1974
User1974

Reputation: 386

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

enter image description here

Related:

Upvotes: 0

astentx
astentx

Reputation: 6751

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a subquery in the where clause:

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

Upvotes: 1

Related Questions