AnsweredAssumed Answered

Passing multiple IDs into WHERE ID IN (?) SQL Statement

Question asked by AndreiSouchinski5081 on May 5, 2016
Latest reply on Mar 11, 2018 by dcollins724067

Hello,

 

I am creating a process which retrieves Organisation records from a database.

One of the WHERE clauses includes a list of Organisation IDs:

fr.business_org_fk IN (123,345,567,890)

 

Is there a way to make all of the values within an IN clause a single parameter?

fr.business_org_fk IN (?)

 

I have tried passing the 123,345,567,890 as string to this parameter (also a string type) but I am getting the following error:

Errors occurred while building or executing SQL statement: ORA-01722: invalid number ; Caused by: ORA-01722: invalid number

 

If I pass a single ID into the IN clause - that works just fine, and only fails when there are multiple IDs delimited by a comma.

 

If there were just a handful of IDs I would set them up as a parameter each, but there about 100 of them and they are constantly changing, so I need this as a process property that can be maintained in a sustainable manner.

 

Many Thanks!

Outcomes