CODEWITHSUNDEEP
oracle-databaseoracle11g
Aaron
Aaron

Reputation: 686

Oracle In Clause not working when using Parameter

I have a Pesky SSRS report Problem where in the main query of my report has a condition that can have more than 1000 choices and when user selects all it will fail as my backend database is Oracle. I have done some research and found a solution that would work.

Solution is

re-writing the in clause something like this 

(1,ColumnName) in ((1,Searchitem1),(1,SearchItem2))

this will work however when I do this

(1,ColumnName) in ((1,:assignedValue))

and pass just one value it works. But when I pass more than one value it fails and gives me ORA-01722: Invalid number error

I have tried multiple combination of the same in clause but nothing is working

any help is appreciated...

Upvotes: 0

Views: 238

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30765

Wild guess: your :assignedValue is a comma-separated list of numbers, and Oracle tries to parse it as a single number.

Passing multiple values as a single value for an IN query is (almost) never a good idea - either you have to use string concatenation (prone to SQL injection and terrible performance), or you have to have a fixed number of arguments to IN (which generally is not what you want).

I'd suggest you

  • INSERT your search items into a temporary table
  • use a JOIN with this search table in your SELECT

Upvotes: 1

Related Questions