0

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...

Improve this question

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I cant do the Temp Table as it will be running from the SSRS report and multiple users can access this report at the same time. I have thought of that option before.
A temporary table is specific to a given database session. Unless your SSRS report uses connection pooling, it should work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.